I’m love to use Airtable everymonth to publish invoice and user’s portal.
Well, I found a problem with “rounddown” calculate.
When I input the date like following, the result was not correct.
Price field = 147.70
Quantity field = 50
Subtotal field[ rounddown(Price * Quantity,0) ] = 7384.00
I checked with Excel spreadsheet, the result was 7385.00 !!!
And I also checked with calc app, the result was 7385 also.
I can’t understand why it doesn’t work the same.
And I’d like to fix it ASAP.
Please help me…
Looks like a major bug with Airtable.
I would report this as a bug to firstname.lastname@example.org, but I wouldn’t hold your breath on them fixing this. I have rarely — if ever — seen them fix any bugs I have ever reported to them in years.
There is a floating point point in 147.7. It is the seven tenths that makes the number a float instead of an integer. While seven tenths is a terminating decimal in base 10, it is a repeating decimals in binary (0.10110011001100110011…). The computer has to chop off those repeating decimals at some point, and ends up with a slightly smaller number.
I don’t know why Excel and calculators handle this differently from Airtable. Maybe they do internal rounding, allocate more storage to floats, or something.
It’s because Microsoft decided to branch away from the IEEE 754 standard and came up with a little magic that makes the bug less likely to appear. However, the bug is still there - the odds of seeing it are simply lower. (see the section " Cases in which we do not adhere to IEEE 754") I think they also have some underlying magic concerning mantissa storage that’s way over my head.
Calculators have them as well, but depending on the register architecture, it’s almost impossible to see the bug itself. One way to see it is to iteratively calculate roots of a number and then square the results again. If you do it often enough you will get a different result than the input due to numerical errors, this doesn’t only have to do with the floating point representation but also with inaccurate root algorithms used in the calculator.
I read Microsoft’s “branch away from the IEEE 754” but it is so complicated for me hahaha.
I can not understand about floating error but I could solve the problem with using rounddown(round(qty * price,5),0).
So it’s okey for me.
Thank you very much for your kindness!