Oct 10, 2022 05:53 AM
Hello everyone.
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…
Oct 10, 2022 06:27 AM
Looks like a major bug with Airtable.
I would report this as a bug to support@airtable.com, 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.
Oct 10, 2022 06:31 AM
Thank you very much for your kindness!
I’ll adjust it by myself with hoping Airtable team to fix it;-)
Oct 10, 2022 06:46 AM
You’re welcome! Be sure to email them to alert them to this!
Oct 10, 2022 10:47 AM
If it’s caused by the floating point bug, it’s not likely to be fixed soon.
Oct 10, 2022 06:14 PM
But doesn’t the floating point bug only crop up when you’re dealing with decimals? In this case, everything ends in .0000000 all the way to infinity. There’s no 1 at the end.
Oct 10, 2022 07:58 PM
I am unable to reproduce this. What happens if you delete the values and retype them?
Oct 10, 2022 08:47 PM
I’m able to reproduce this. See screenshots below.
Oct 10, 2022 09:47 PM
Now I can reproduce it.
I’m going to go with the floating-point issue that @Bill.French pointed out.
My solution is to put in some intermediate rounding:
Oct 11, 2022 03:50 AM
How can it be a floating point bug if there are no floating points?
I’m assuming that this ROUNDDOWN bug also affects ROUNDUP as well.
Oct 11, 2022 04:34 AM
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.
Oct 11, 2022 04:37 AM
Very interesting. Thanks for the explanation!
Oct 11, 2022 05:39 AM
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.
Oct 11, 2022 05:40 AM
Ha ha! I want the green check mark and an @ reference.
Oct 11, 2022 05:55 AM
I can’t give you the green check mark (only the original poster (@global) can do that. I did edit my post to give you the @ reference.
Oct 11, 2022 06:16 AM
If they could, they would have “fixed” it long ago. :winking_face:
@kuovonne quickly validated my hunch and unfortunately, this is not Airtable’s bug to fix; you need to find a way to live with it as every javascript developer on the planet has for almost 40 years.
Oct 11, 2022 07:57 AM
My entire life’s perspective of 0.1 + 0.2 = 0.3 has been shattered! :stuck_out_tongue_winking_eye:
Oct 11, 2022 08:04 AM
I have only 10 words -
Every sufficiently powerful formal system is either inconsistent or incomplete. [1]
Oct 19, 2022 01:23 AM
Hi everyone!
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!