Skip to Navigation

Knowledge Base

Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.

Why are there discrepancies when rounding and comparing floating point numbers?

QuickBase users may notice small discrepancies when rounding and comparing floating point numbers. For example, Round(37.785,0.01) returns 37.78 instead of 37.79. This is not a QuickBase-specific issue; the discrepancies happen because some floating point numbers cannot be represented exactly in the binary format required by computers and are instead approximated. 

QuickBase follows the IEEE 754 Standard for Binary Floating Point Arithmetic, and stores decimal numbers, like 0.1, as “doubles” or floating point numbers, which are binary representations of fractional numbers. Unfortunately, all decimal fractions cannot be represented exactly as binary fractions and need to be approximated to the nearest floating point value.

In decimal base, 1/3 is a decimal followed by infinite 3s. The more 3s we use, the more the number represents the fraction 1/3.  However we can never represent 1/3 exactly in this form.

Similarly, in binary, numbers like 0.1 cannot be represented exactly in computers because they don’t have a terminating representation. In binary, the decimal number 0.1 is 1 .100110011001100110011…. with infinite repetition. The computer thus has to make approximation for these numbers. Since the amount of space given for doubles is 52 bits, the nearest representable number is 1.1001100110011001100110011001100110011001100110011010. The decimal equivalent of this binary is 0.1000000000000000055511151231257827021181583404541015625, which is not exactly 0.1.

Because of the approximated representation, users may notice small discrepancies when using the Round(x,y) QuickBase function with floating point numbers.

Users also may notice discrepancies when comparing floating point numbers.  A user cannot simply expect floatA == floatB to always return “true” even if the decimal equivalents are true.  For example:

  1. Create a Numeric field and a Formula field.

  2. Set decimal places to be 20 for both.

  3. For Numeric field, enter 1234.6, and for Formula field enter Round(1234.56789,0.1).

  4. The result for the Numeric field is 1234.59999999999, even though you typed in 12.6 and the round’s result is 1234.6000000000001, therefore the comparison returns “false”.

Note that QuickBase only stores 52 bits for doubles, (roughly 15 digits). QuickBase, therefore, is precise up to 15 digits for fractional numbers. This means that QuickBase returns the same value for up to 15 digits each time. A user cannot extend the precision value of a floating point number using the field’s Decimal setting and expect to get consistent results. For example, if a user enters 37.785 and asks QuickBase to display up to 30 decimal places, QuickBase can only be guaranteed to be precise for up to 15 digits.

Additional References:


To anyone else who was as

To anyone else who was as frustrated with this rounding issue as I am, I've found a fix.

I had a formula calculating a total dollar amount, and because of rounding issues we were occasionally a cent off from where we should've been - not a big deal, but it was raising red flags and I wanted to eliminate it.

My solution was to add 0.0001 to the amount before rounding.  That's enough to bring, for example, 10.31499999999 up to 10.315099999999 (and let it round correctly), but not large enough to affect anything else - a field at 10.3150000000001 would just become 10.315100000001, and get rounded out either way to 10.32.  

You just have to make sure that whatever constant you add is low enough not to affect the real numbers.  In our case we'll never have anything other than whole and half cents (because payroll is done in half-hour increments), so this is perfect.  

Thanks for the information

Thanks for the information Andrew! Since currencies typically only need to be accurate to the closest 0.01, this sounds like a good way of avoiding floating-point errors for that use case.



QuickBase Staff

Post new comment

The content of this field is kept private and will not be shown publicly.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.

© 2016 QuickBase, Inc. All Rights Reserved.

Online Database VeriSign Secured Web Based Software TRUSTe Certification Online Database SSAE Audit