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

[Stefan_Lenz]

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

[Stefan_Lenz]

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…

[W_Vann_Hall]

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

[Mike_McLaughlin]

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.

[JIMz_NG]

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.

[Jacob_Clenney]

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.

[Zhanna_Hale]

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.

[Hendrik_Yang]

Has this been resolved?