Floating Point in Quick Base-Why are there discrepancies when rounding and comparing floating point numbers?

By Evan Martinez posted 09-12-2019 11:43


[The Quick Base Knowledge Base is your library of frequently-asked questions that help you better customize your apps to solve your business problems.]

Quick Base 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 Quick Base-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. 

Quick Base 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) Quick Base 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 Quick Base only stores 52 bits for doubles, (roughly 15 digits). Quick Base, therefore, is precise up to 15 digits for fractional numbers. This means that Quick Base 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 Quick Base to display up to 30 decimal places, Quick Base can only be guaranteed to be precise for up to 15 digits.

Additional References: