Basic formula accuracy

Tags: #<Tag:0x00007efc61619d10>
#1

I am using formulas / RuleJS. If, as a specific example, you enter:
= 1.23 + 45.66
into a cell, it displays 46.88999999999999 as the value. Other values may give “correct” result, e.g.
= 1.00 + 45.89
displays 46.89.

I am deliberately not defining the cell type as “numeric” here. I accept that if I give it type: "numeric", format: "0.00" the first case will be output as 46.89. That is not my point.

I totally understand that floating point numbers are stored/represented “approximately”. But I have never seen a language give such a non-accurate result with numbers like these, and find it hard to believe that JavaScript is “off” on this particular calculation. Is this inaccuracy coming from RuleJS? Is numeral.js still involved if the cell is not defined as numeric? This issue means that essentially you can never dare use a formula anywhere without defining the cell as type numeric, with all the consequences that entails…

1 Like
Issue with wrong numbers rounding using numeric renderer(numro.js most likely)
#2

Hi @jon
numeral.js will be changed by the numbro in the nearest release. We’ll check if this case occurs there also.

The thing that concerns me is that you gave us a sum example - mostly the numbers like 46.88999999999999 can be seen while dividing.

#3

Hi @jon
Unfortunately this bug is still present.
I’ve checked it with the newest version at https://github.com/handsontable/handsontable/issues/2777
If you would like to track our progress related to this bug , please leave a comment at https://github.com/handsontable/handsontable/issues/2777

#4

It’s a JS issue. If you type this in the console, you get:

1.23 + 45.66
46.88999999999999

#5

thanks for sharing @pdemilly!

#6

Well, I have never been a great fan of JS, and if it is inaccurate like this in adding at just 4 significant digits I am even more dissatisfied.

I realise this is OT as far as Hot is concerned, but, given my findings above, does that mean that in JS:
1.23 + 45.66 != 1.00 + 45.89
(or, is its floating point != comparison an “approximate” operator)? Because if that is the case, it does not bode well for spreadsheet calculations…

#7

If I can add my two cents @jon I’m leaving https://github.com/handsontable/handsontable/issues/2777 opened (an an improvement suggestion). Maybe we cannot fix it but for sake of formulas and summary calculations we can try to find a workaround.

#8

Jon,
1.23
This is true for all languages using floating point representation. (c, python, javascript) will all return the same result. Just use integer and do your displaying by dividing by 100 at the last minute.

123 + 4566 != 100 + 4589
False

#9

@pdemilly

This is true for all languages using floating point representation. (c, python, javascript) will all return the same result.

No, they will not. Why don’t you try my example in C, and see for yourself? I am well aware of floating point approximations, and carefully wrote “I totally understand that floating point numbers are stored/represented “approximately”. But I have never seen a language give such a non-accurate result with numbers like these” The issue is the JavaScript inaccuracy with such “modest” numbers, rather than in the 10th significant digit of some number.

Just use integer and do your displaying by dividing by 100 at the last minute.

Umm, this is Handsontable, the user enters the numbers/formulas into cells, its (JavaScript) code does the math. I cannot make it do integer stuff instead, and I don’t think users will be too happy if you tell them they should do all their calculations that way …

#10

[EDIT]
For the first point, whether this is any worse in JavaScript than some other language, although my particular example seems to work OK in another language but not JavaScript, I’m willing to accept that may be example/compiler specific. From what I can gather now, it seems JS uses same precision (“IEEE-754”) as, say, Java, which is fair enough. If that is the case, I withdraw my whinge specifically about JavaScript.

What matters here is that Handsontable is a spreadsheet application, and of course I am not in control of its code to do the math. So I’m looking to the Hot devs to address this issue for such “reasonable” numbers — for example, “money” numbers — as I am illustrating.

Since they are, apparently, rewriting the Hot formula code as we speak, now might be an ideal time to put something in to deal with this. Previously Hot was stuck with whatever RuleJS did, now I understand them to be rewriting with their own code. Posts like http://stackoverflow.com/questions/1458633/how-to-deal-with-floating-point-number-precision-in-javascript and http://stackoverflow.com/questions/588004/is-floating-point-math-broken give a host of suggestions, including the specific use of JS libraries like https://github.com/MikeMcl/big.js/ if desired.

How’s about: I would expect/hope a spreadsheet could do arithmetic accurately up to, say, 9,999.99 — that’s 2 decimal places in 6 significant digits. Seems reasonable and useful for a spreadsheet?

#16