Difference between two currency fields is "7.275957614183426e-12"

Topic Labels: Data Formulas
431 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

My table has four fields: Total Price, Total Paid, Amount Owed, and Payment Status. When the value of Total Price and Total Paid are the same, the value in the Amount Owed field, predictably, shows "$0" – however when I use the value of {Amount Owned} in a custom formula for the Payment Status field, it shows up as "7.275957614183426e-12" i.e. a near-zero value, but not zero. This is causing my formula to break, although I can work around it by checking if the value is "< 0.01" instead of "== 0". 

This appears to be a bug in AirTable, 

Total price is a manually entered currency value.

Total Paid is a rollup field, summed from a different table using SUM(values)

Amount Owned is a simple formula subtracting Total Paid from Total Price

And Payment Status is a formula that looks like this:

IF({Total Price} > 0,
  IF({Amount Owed} > 0,
    IF({Total Paid} > 0,
     "Partially Paid",
   "Fully Paid"
1 Reply 1

I never met exactly this bug, but similar things happen from time to time due to the way how floating point data stored in memory.
Try to examine source rows of rollup, and check where it can be from.
The simple way to solve is to wrap your Amount Owned formula into Round({Current_formuila},2) , for example.  But I would better find a reason of failure, than cure its symptoms.