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

[global]

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…

[kuovonne]

ScottWorld:

How can it be a floating point bug if there are no floating points?

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.

[ScottWorld]

Very interesting. Thanks for the explanation!

[Bill_French]

kuovonne:

I don’t know why Excel and calculators handle this differently from Airtable.

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.

[Bill_French]

kuovonne:

I’m going to go with the floating-point issue that Bill pointed out.

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

[kuovonne]

Bill.French:

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.

[Bill_French]

global:

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

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.

[ScottWorld]

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

[Bill_French]

I have only 10 words -

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

[global]

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!