Help

Re: Why "Rounddown" result is not same with Excel spreadsheets?

1695 4
cancel
Showing results for 
Search instead for 
Did you mean: 
global
5 - Automation Enthusiast
5 - Automation Enthusiast

image

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…

18 Replies 18

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.

global
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you very much for your kindness!

I’ll adjust it by myself with hoping Airtable team to fix it;-)

You’re welcome! Be sure to email them to alert them to this!

If it’s caused by the floating point bug, it’s not likely to be fixed soon.

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.

I am unable to reproduce this. What happens if you delete the values and retype them?

I’m able to reproduce this. See screenshots below.

Screen Shot 2022-10-10 at 11.46.15 PM

Screen Shot 2022-10-10 at 11.46.35 PM

Now I can reproduce it.
I’m going to go with the floating-point issue that @Bill.French pointed out.

image

My solution is to put in some intermediate rounding:
image

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.

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.

Very interesting. Thanks for the explanation!

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.

Ha ha! I want the green check mark and an @ reference.

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.

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.

My entire life’s perspective of 0.1 + 0.2 = 0.3 has been shattered! :stuck_out_tongue_winking_eye:

I have only 10 words -

Every sufficiently powerful formal system is either inconsistent or incomplete. [1]

global
5 - Automation Enthusiast
5 - Automation Enthusiast

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!