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:
Create a Numeric field and a Formula field.
Set decimal places to be 20 for both.
For Numeric field, enter 1234.6, and for Formula field enter Round(1234.56789,0.1).
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.