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
5998
1

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.