Do I really have to manage number precision myself?

[WilliamPorter]

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.

[kuovonne · Accepted solution]

WilliamPorterTech:

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

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.

[WilliamPorter]

@kuovonne,

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.

[kuovonne]

WilliamPorterTech:

And one thing I don’t understand is why this problem occurs in some calculations but not most .

It just depends on the floating point precision error for that particular calculation.
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.