Bug 157856 - BASIC macro not recognising sum 0.05+0.90 equals 0.95
Summary: BASIC macro not recognising sum 0.05+0.90 equals 0.95
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2023-10-20 12:00 UTC by cppjohn
Modified: 2023-10-20 15:01 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

LibreOffice Calc sum bug (17.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-10-20 14:10 UTC, cppjohn
Excel doing the same (76.74 KB, image/png)
2023-10-20 15:01 UTC, Mike Kaganski

Note You need to log in before you can comment on or make changes to this bug.
Description cppjohn 2023-10-20 12:00:52 UTC
It looks like I've found a bug when running a BASIC macro on LibreOffice Calc ( x86_64), which doesn't fault when I run it the same macro on Apache OpenOffice calc (4.1.5 Build 9789 Rev 1817496 2017-12-11 17:25).
Two cells are summed; values 0.05 and 0.9, giving a sum of 0.95.
When LibreOffice runs the macro it returns a false to [0.05 plus 0.9 equaling 0.95], whereas OpenOffice returns true.
All involved cells are formatted as Category > Number and Format > General.
I tried changing “Decimal places:” under Options but that didn't make any difference.
The workaround solution I found was changing the first cell value from “0.05” to “=0.95-0.9” (not including the “” ofcourse).
I also tried “=1-0.95” which interestingly did not work.
The following might be a clue to the underlying bug: for the entries that didn't work (“0.5” and “=1-0.95”), down the bottom right hand side of the window it says “Sum: 0.05” as you'd expect, but for the entry that made it work (“=0.95-0.9”) it says “Average: 0.0499999999999999; Sum: 0.0499999999999999”. 

The “Sum:” value for “=0.95-0.9” on OpenOffice calc is 0.05 instead of  0.0499999999999999.

Steps to Reproduce:
1.Create 3 number cells with the following values: 0.05, 0.9, 0.95.
2.Run a BASIC macro to report if the first two cells equal the third cell.
3.It incorrectly behaves as if the 0.95 cell is not equal to 0.05 + 0.9 (returns false).
(More info in Description.)

Actual Results:
The only way I could get the macro to work was by changing the cell value from "0.05" to "=0.95-0.9".

Expected Results:
OpenOffice's Calc's BASIC macro's "if" statement to return true regardless of the first cell being "0.05" or "=0.95-0.9", as it does in OpenOffice calc.

OpenOffice's Calc's "Sum: " value should return "0.05" to "=0.95-0.9", as it does in OpenOffice calc.

Reproducible: Always

User Profile Reset: No

Additional Info:
Version: (X86_64) / LibreOffice Community
Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333
CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-US
Calc: threaded
Comment 1 cppjohn 2023-10-20 12:08:03 UTC
Expected Results:
**LibreOffice's Calc's
Comment 2 Mike Kaganski 2023-10-20 12:16:03 UTC
The macro you provided doesn't want to run on my system ;)
Comment 3 Mike Kaganski 2023-10-20 12:33:50 UTC
(In reply to cppjohn from comment #0)
> The “Sum:” value for “=0.95-0.9” on OpenOffice calc is 0.05 instead of 
> 0.0499999999999999.

It is indeed unclear if this occurrence of "OpenOffice" in comment 0 actually meant OpenOffice (better use AOO, to avoid confusion in the future), or was meant to refer to LibreOffice (LO). But from context, it seems to mean AOO, as showing "correct" answer; and so, bug 107953 seems relevant to *this specific bit* related to display.

This faq has a general overview: https://wiki.documentfoundation.org/Faq/Calc/Accuracy

It indeed not only relates to Calc, but to any floating-point processing. It might happen, that we dropped some rounding, that allowed to compare some different floating-point values equal; this would need checking (so please do attach a sample ODS, with the macros; it is unclear what you did to get the values from the cells - e.g., one could take strings, and convert to numbers; however "clear" the textual description is - it is unreasonable to ask people to re-invent the macros, and *hope* they have the same as you have).

This calculator allows to see the binary representations of doubles: http://weitz.de/ieee/. It helps to see, that 0.05 + 0.90 results in 0.9500000000000001 (binary 0b0011111111101110011001100110011001100110011001100110011001100111), while 0.95 is 0b0011111110101001100110011001100110011001100110011001100110011010 (the difference is in the last digit).
Comment 4 cppjohn 2023-10-20 13:00:56 UTC
Yes, sorry, I meant LibreOffice Calc is giving the error and OpenOffice Calc does not have this issue.

The macro simply adds the first two cells (30 and 31 below) together via variable 'balance' to confirm that the sum equals the value of the third cell (35 below).
I've used it for years on OpenOffice Calc no problem and just started using LibreOffice Calc this week and it gave this error.

The BASIC macro If statement correctly returns true in OpenOffice Calc but incorrectly returns false in LibreOffice Calc.
Here is the code of concern:

Dim balance
balance = 0
Cell3 = LogSheet.getCellByPosition(30, Sub2Counter) 'The 0.05 value cell.
balance = balance + Cell3.Value 
Cell3 = LogSheet.getCellByPosition(31, Sub2Counter) 'The 0.90 value cell.
balance = balance + Cell3.Value
Cell3 = LogSheet.getCellByPosition(35, Sub2Counter) 'The 0.95 value cell.
If balance = Cell3.Value Then                       ' This is returning a wrong false in LibreOffice Calc.
Else  ' report an error if the tally doesn't match what's on the Log sheet

It worked fine with other decimal-place values. Not sure why these played up.
Comment 5 cppjohn 2023-10-20 14:10:52 UTC
Created attachment 190345 [details]
LibreOffice Calc sum bug
Comment 6 Mike Kaganski 2023-10-20 14:18:15 UTC
Ah fun!
This is not a bug at all. In fact, this now works correctly, and it didn't before. Specifically, this change of behavior happened in v.7.0, as the result of fixing bug 130725, in commit 1782810f886acd26db211d8fdd7ae8796d203c57.

What happened before was that the *string* "0.95" (which you entered into cell, and which was stored into/read from the spreadsheet file) was interpreted incorrectly by the old code, resulting in the value


instead of


which is what we get now. You may see, that the old value differs from the ideal "0.95" by ~6.7E-17, while the new value differs by ~-4.4E-17 - so the new value is the correct closest representation of the number 0.95, which itself is not representable in IEEE 754 double-precision binary number.

When later the subtraction happened, with its own expected error (see the FAQ mentioned in comment 2, and the Wikipedia page referenced from there), the result happened to match the wrong representation of 0.95 - by pure luck.

In general - it is the programmer's error to compare floating-point numbers using plain "="; one needs to use comparison with an epsilon in this case - e.g.,

    If Abs(balance - CellA3.Value) < 1E-10 Then
Comment 7 cppjohn 2023-10-20 14:39:19 UTC
Thank you for your help.
I recall, around 8 years ago, using something like [If difference < 0.0000001] for something similar.

Nonetheless, it should be said that 
equals 0.95, but that 0.95 less 0.9 does.

In my mind, it should interpret 0.95 as 0.95, not 0.9499999999999999555910790149937383830547332763671875.

If I had a copy of Excel (which I don't) I'd try it and see if it handles it any differently.
Comment 8 Mike Kaganski 2023-10-20 15:01:37 UTC
Created attachment 190352 [details]
Excel doing the same

(In reply to cppjohn from comment #7)
> Nonetheless, it should be said that 
> 0.95000000000000006661338147750939242541790008544921875
> nor
> 0.9499999999999999555910790149937383830547332763671875
> equals 0.95, but that 0.95 less 0.9 does.
> In my mind, it should interpret 0.95 as 0.95, not
> 0.9499999999999999555910790149937383830547332763671875.

Please read the FAQ. The number 0.95 is *not* representable in the binary numbers used in Calc (and Excel, and Gnumeric...). It is state of the industry. It is something one must be prepared to. No amount of imagining what it should, can change the current hardware imitations.