This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Floating Point Error - Addition/Subtraction

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
3328
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 01, 2017 04:37 PM

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

Reply

17 Replies 17

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 02, 2017 08:21 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 02, 2017 10:43 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 24, 2018 04:59 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 24, 2018 03:12 PM

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`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 25, 2018 12:21 AM

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.

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 25, 2018 12:24 PM

`-`

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 27, 2018 01:41 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 27, 2018 01:43 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 27, 2018 01:53 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 27, 2018 01:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 01, 2018 05:14 AM

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…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 01, 2018 05:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 18, 2019 02:08 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 08, 2019 03:35 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 28, 2020 04:01 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 11, 2021 04:17 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 23, 2022 01:26 AM

Has this been resolved?