Floating Point Error - Addition/Subtraction

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

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

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?

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

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.

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

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.

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

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.

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.

Thank you everyone who shared their thoughts, I have an acceptable solution.

Hi all,

I hit a snag - in my table 0.00 is a perfectly valid reference temperature, but the condition if({Reference Instrument Temperature}) now evaluates to false. This is why the test for an empty string “” would be a more useful condition than testing for a 0 value. I have solved this by entering a small value like 0.00001 for the reference temperature and letting the rounding take care of this, but I am now waiting for what other things will go wrong.

Watch this space…

Stefan -

A quick test implies this version of the formula will work, even with a {Reference Instrument Temperature} of zero:

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

It appears an explicit test for BLANK() will not match a value of 0.00; at the same time, testing for BLANK() does not cause the implicit conversion to a string that caused the initial FP errors. (Caveat: I haven’t tested this to any great extent. I also noticed, thanks to a typo, that a value of 0.00 does test positively against IF({Reference Instrument Temperature}=BLANK(), which suggests Airtable Development still needs to look into this.)

This is still a problem, I’ve been able to boil it down to a very simple example. Of If(A-B<=.001,“TRUE”,"FALSE) evaluates as false where A and B are entered as .100 and .101. respectively

My calculations involved 5 fields and many other functions, so this was very hard to nail down originally as it was soo unexpected. My workaround is to subtract a small (0.00000001) number at the last step, but it shouldn’t be necessary to have to do so in such a high level app as Airtable. :pensive:

As i was writing this :thinking: , I realized you can also used the ‘Round()’ function to truncate the trailing digits past your needed precision. The subtracting a small number won’t ever let you be able to evaluate (repeatably) if 2 small numbers are equal, but the round() function I think serves as another :expressionless: workaround.

just did a quick test like yours and verified the bug still exists. kind of annoying to know they have pretty much ignored what you said here, which i absolutely agree with.

asking the users / customers to mess around with formulas instead of addressing the well-known bug is just disappointing.

I just ran into the same issue today, which means this issue has yet to be addressed. I simply wanted to remind the community this is still an issue. Luckily for me, inserting the round() function allows me to move past the issue. That said, I agree with Mike, this isn’t something you should have to come up with a work around.

Unfortunately for me, I’ve been dealing with this issue since I started using Airtable for currency calculations. It is immensely frustrating every time this issue rears its head that I come to the forums and am reminded how long this error has existed.

There are a not-insignificant number of fields in my database that exist solely because I need my calculations to be both accurate and user-friendly for my non-database experienced coworker. It is ludicrous that the formulas cannot be both. And I have spent a not-insignificant number of hours fiddling with my database and trying to find workarounds for this issue.

I have learned more about the mathematical concept of rounding than I ever thought existed or could ever want to know, in the hopes that some of that information could help me optimize my database. Maybe it’s just because I’m fresh off reading another post that dealt largely with the Airtable staff’s silence over the years on many bugs and issues like this, but I am seriously considering leaving Airtable altogether for a better, more accurate, solution.

Has this been resolved?