Floating Point Error - Addition/Subtraction

[Lane_United]

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

[Kasra]

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})

[Lane_United]

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

[Stefan_Lenz]

Hi,

Unfortunately, this behaviour is still present and causing some issues.

I have to calculate the difference between two numbers with 2 decimal place resolution.

The deviation uses the following formula:
{Reference Instrument Temperature}-{DUT Temperature}, also with a 2 decimal point resolution.

In the above screenshot the deviation is calculated correctly.

When, however, I simply put an if statement around the calculation to catch a missing Reference Instrument Temperature, as here:

IF({Reference Instrument Temperature}=""," ",{Reference Instrument Temperature}-{DUT Temperature})

I get the aforementioned floating point issue, although the calculation itself has not at all changed.

How can this be resolved? I need to report these numbers with two decimal points (even if 0), so the ROUND function doesn’t help. I assume this stems from the fact that the result of the calculation is no longer a number, but how can I format the result to be reported with 2 decimal places?

[Andy_Lin1]

The ROUND function can takes a “precision” argument, which, for two decimal places is “2”. Does the below formula work for you?
=ROUND({Reference Instrument Temperature}-{DUT Temperature},2)

Alternatively, there’s the… classic but dumb way of doing it, which is to multiply the numbers by 100 before any arithmetic, then dividing it by 100 at the end:
=(ROUND({Reference Instrument Temperature}*100,0)-ROUND({DUT Temperature}*100,0))/100

[W_Vann_Hall]

The problem seemingly is caused by the implicit conversion to (and from?) a string value¹ — which I hope points Airtable development towards a fix. When I use your IF() formula, I also get floating point errors; oddly, I get different FP errors:

However, if I use this formula —

IF(
    {Reference Instrument Temperature},
    {Reference Instrument Temperature}-{DUT Temperature}
    )

— I get the expected values:

The positive test for {Reference Instrument Temperature} will correctly yield a blank cell when no {Reference Instrument Temperature} exists.

---

1. This also appears to factor into @Lane_United’s initial report.

[W_Vann_Hall]

Not having anything to do with Airtable, but is it correct to speak of the deviation in your fourth row as being -0.40? Shouldn’t it simply be 0.40? If so, you can wrap your equation with ABS() to get the absolute value… . (Similarly, -0.00 in the third row is also wrong. I have no idea how to correct it, though, as I’m not sure how you managed to get it. Are your measurements recorded at a higher precision than displayed?)

[Stefan_Lenz]

Hi, thanks for your comment, in a calibration environment the deviation is reported such that it is always added to the device under test; that means the deviation needs to carry the sign. This is so that a user knows if their instrument overreads or underreads.

Interesting that there seems to be a difference between the floating point errors, maybe this calculation is done locally and processor dependent. The reason I get a -0.00 is that the actual reported value is -00199… which after rounding is still negative. It is really odd as it means that the same two floating point numbers in two different fields get stored with different values. Yours seems OK, as you get a zero result.

[Stefan_Lenz]

Hi, thanks, that is a great solution, I guess it works because the result is still a number, which can be formatted, whereas my original IF statement used a string in one of the results, hence the formatting would fail. Thanks for this solution.

[Stefan_Lenz]

Hi Andy,

This solution works, but only if the conditional if statement doesn’t convert to a string. If it does, the decimal place of the rounding can be different, depending on the result, for example here:

This becomes a bit of an issue when you always want to report to two decimal places, even if the number only has one significant decimal place.

W_Vann_Hall’s solution gives the correct result, as it can deliver an empty cell test yet still keep the result as a number.