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: Do I really have to manage number precision my...

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

Solved

Jump to Solution

0
2231
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

Mar 31, 2020 11:34 AM

This is odd.

The display field for one table in the base I’m working on shows a concatenated string that represents each record’s basic uniqueness. At the end of the string, there is a reference to a charge. Should look like “$121” etc.

For most of the 1000 or so recs in the table right now, that’s how it’s working. But I noticed a few records that were displaying something like “$121.000000001”. Maybe 5% of my records are showing that, maybe fewer.

The display field’s calc includes a reference to a field ‘ChargeX’, a formula field. ChargeX = HowMany * Rate. The values in HowMany and Rate are not always integers but they are *usually* integers and they are integers in the cases where this display problem occurs. So 1.1 * $110 returns ‘$121.00’. The *formatting* of the ChargeX field is set to Currency with a precision of 2 (ex: ‘$5.00’).

**So I’ve got a nice clean $121.00 sitting in the ChargeX field. But when the display field for the record grabs that value and sticks it at the end of the string, it becomes ‘$121.000000001’.**

(If it matters I’m NOT actually counting the zeros. I’m just illustrating the idea.)

I am aware that, at a deep level, doing decimal math with binary computers is tricky, but I thought a consumer oriented app like Airtable would take care of that sort of thing for us. Was I wrong? Or am I going about my reference incorrectly?

Thanks in advance,

William

p.s. I did read old threads about how setting precision was destroying values. I don’t *think* that’s what is happening here.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

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

Mar 31, 2020 12:28 PM

Even though humans look at this and know that `1.1 * 110`

will result in an integer, the computer doesn’t know that. It knows that `1.1`

is decimal and thinks that the result can be a decimal, and it is subject to the floating point precision issue.

Even though the currency formatting of ChargeX shows the number formatted nicely with two decimal places, it is still storing the calculated value with lots of decimal places. When another formula field references ChargeX, it uses the stored value, not the formatted value.

So, sadly, yes, if you are going to eventually concatenate the number with a string, you do have to manage number precision yourself. One option would be to use the `ROUND()`

function.

You can use it either when you do the initial calculation

```
ROUND(HowMany * Rate, 2)
```

or when you display, or anywhere else in the chain of formulas

```
ROUND(ChargeX, 2)
```

or use one of the pretty print routines.

Reply

3 Replies 3

Solved
See Solution in Thread

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

Mar 31, 2020 12:28 PM

Even though humans look at this and know that `1.1 * 110`

will result in an integer, the computer doesn’t know that. It knows that `1.1`

is decimal and thinks that the result can be a decimal, and it is subject to the floating point precision issue.

Even though the currency formatting of ChargeX shows the number formatted nicely with two decimal places, it is still storing the calculated value with lots of decimal places. When another formula field references ChargeX, it uses the stored value, not the formatted value.

So, sadly, yes, if you are going to eventually concatenate the number with a string, you do have to manage number precision yourself. One option would be to use the `ROUND()`

function.

You can use it either when you do the initial calculation

```
ROUND(HowMany * Rate, 2)
```

or when you display, or anywhere else in the chain of formulas

```
ROUND(ChargeX, 2)
```

or use one of the pretty print routines.

Reply

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

Mar 31, 2020 01:54 PM

Once again I am in your debt. Thanks for this response.

I’m aware of the floating point issue. I’ve seen it (I think) in Excel and (IIRC) in SQL databases. On the other hand, I haven’t seen this problem in FileMaker or other apps that I work in for a long time.

And one thing I don’t understand is why this problem occurs in some calculations *but not most*. Consider the screenshot below. This is an unrepresentative sample of my base: this was a rare case where there were two problems on the same screen. What intrigues me is, virtually ALL of the charge values at the end of that calculated string are pulling data from the ChargeX field, which in turn in nearly all cases, is multiplying $110 by a decimal value like 0.9, 0.3, 0.5, 0.6, 1.4 and so on. *But the problem only occurs when the multiplier is 1.1. It does not occur with any other values as far as I can tell – including 2.1, 3.1, etc.

I understand the ROUND() function perfectly well but it’s kind of a pain to have to use it 100% of the time to solve a problem that only occurs slightly less than 5% of the time…

Anyway, I’ll do as you suggest. Thanks again for your generous help.

William

p.s. And thank you for the *pretty print* link! I’ve bookmarked and will read carefully later.

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

Mar 31, 2020 03:19 PM

If the value is 121.0001, you see the decimal. If the value would be 33.00000000000000000000000000000000000000000000000000000001 but Airtable doesn’t store that many decimal places, then you don’t see the problem.