Description: It looks like I've found a bug when running a BASIC macro on LibreOffice Calc (7.6.2.1 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: 7.6.2.1 (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
CORRECTION: Expected Results: **LibreOffice's Calc's
The macro you provided doesn't want to run on my system ;)
(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).
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.
Created attachment 190345 [details] LibreOffice Calc sum bug
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 0.95000000000000006661338147750939242541790008544921875 instead of 0.9499999999999999555910790149937383830547332763671875 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
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 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. If I had a copy of Excel (which I don't) I'd try it and see if it handles it any differently.
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.