Floating Point Error - Addition/Subtraction


#1

There is a floating point bug that I can only get to rear its head in a particular way. I have distilled down to this simple example. Add three particular numbers (haven’t played around with which numbers cause the problem, but these do), subtract the sum from the same number in another field and the result is displayed as zero, as it should. Put an IF statement in another field comparing the result to zero, and the comparison fails. I was able to get the IF statement to display the floating point error by displaying “ZERO” or the calculated result. See below:


I have a larger base where I am adding a number of fields and comparing the result to what it should be and flagging the result if it isn’t the same. This bug is causing a number of false negatives, so a fix would be greatly appreciated!

Love Airtable and I use it for more and more projects everyday, so thank you so much.

-Dave


#2

Hi Dave,

Floating point numbers can be tricky, because the under-the-hood representation isn’t always exactly the same as what you see printed out on the screen. For example, the value in the “Subtraction” field in your screenshot is not exactly 0.00000 — there are a few digits further to the right, but they’re getting rounded to zero.

When comparing floating point numbers, it’s a good idea to use ABS to compare the absolute difference against some threshold for equality, e.g.:

IF(ABS({Amount} - {Sum}) < 0.001, "ZERO", {Amount} - {Sum})


#3

Hi Kasra,

Thank you for your response. This is exactly why I called it a floating point error. The numbers 41.1, 14.99, and 329 are being summed as 385.090000000000005684341886080802. I understand the rounding concept and wasn’t implying the number in the subtraction field wasn’t being rounded. The three numbers aren’t being brought in from elsewhere and being rounded for representation on the screen, they’re typed in. That -5.684341886080802e-14 isn’t being carried in one of the three numbers, it’s being created during summation.

I understand that things are being done under the hood with floating point numbers being represented in a binary system, and rounding is being performed everywhere. I will contend though, that your workaround should be built-in if we were to change the format of the numbers to currency (and to the precision defined for decimals too, honestly), but as you can see below, that is not the case. Rounding is obviously being done or we wouldn’t get ZERO in line 4, but the granularity seems off. It seems rounding to a trillionth of a cent for currency should be ok and would appear to fix this problem.

Thanks again for your response. I will use your workaround for now.

-Dave