Bug 141609 - LibreCalc IF function incorrectly evaluates negative numbers
Summary: LibreCalc IF function incorrectly evaluates negative numbers
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2021-04-10 17:41 UTC by Chuck
Modified: 2021-04-12 12:25 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Calc file (15.79 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-04-10 17:44 UTC, Chuck

Note You need to log in before you can comment on or make changes to this bug.
Description Chuck 2021-04-10 17:41:30 UTC
My IF statement, comparing a negative sum in one cell to the sum of two negative numbers from other cells, returns a false response when it should return a true response. The IF statement is:
=IF(B18=B39+B59,"OK","No Match")
where B18 contains the formula =B8-B17 equal to (1,629.93), with B8 containing =SUM(B3:B7) equal to 137,962.60, and B17 containing =SUM(B10:B16) equal to 139,592.53;
where B39 contains the formula =B29-B38 equal to (1,230.69), with B29 containing =SUM(B24:B28) equal to 136,936.06, and B38 containing =SUM(B31:B37) equal to 138,166.75;
and where B59 contains the formula =B49-B58 equal to (399.24), with B49 containing =SUM(B44:B48) equal to 1,026.54, and B58 containing =SUM(B51:B57) equal to 1,425.78.
So,B18=B39+B59 is (1,629.93)=(1,230.69)+(399.24), and is true. The IF statement should return "OK" but it doesn't. Instead, it returns "No Match".
If I change the IF to read =IF(B18=B39+B59,"OK",B39+B59), it returns (1,629.93). So it is doing the simple arithmetic correctly, but it does not recognize that (1,629.93) is equal to (1,629.93). 

Steps to Reproduce:
1.See the attached Calc spreadsheet. The formula is in B79. The modified version is in B80.

Actual Results:
The IF statement is evaluated as false. Similar statements (in cells B64 through B79) evaluating components are all true. It appears that the problem only occurs when negative numbers are compared.

Expected Results:
The software should recognize that B18=B39+B59 is (1,629.93)=(1,230.69)+(399.24) and is true.

Reproducible: Always

User Profile Reset: Yes

Additional Info:
Version: (x64)
Build ID: 64390860c6cd0aca4beafafcfd84613dd9dfb63a
CPU threads: 20; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL
Comment 1 Chuck 2021-04-10 17:44:22 UTC
Created attachment 171085 [details]
Calc file
Comment 2 Chuck 2021-04-10 23:01:55 UTC
Later, I added 3 more columns of data, using the same format. Where additions and subtractions occurred, all numbers evaluated as positive numbers. But the same problem occurred in 2 out of 3 columns. So the problem is NOT restricted to negative numbers.
Comment 3 laurent combe 2021-04-11 06:53:28 UTC
it's more a rounding calculation issue (well known in spreadsheet)

in B79 if you replace your original condition B18=B39+B59 by B39+B59-B18<0,01 is it an acceptable solution for you ?
Comment 4 [REDACTED] 2021-04-11 10:26:28 UTC
Use "=IF(ROUND(B18;2)=ROUND(B39+B59;2);"OK";"No Match")"
Comment 5 Chuck 2021-04-12 07:22:56 UTC
I think it's odd to have to tell the program to round to 2 decimal places when all of the numbers in all of the cells are set to 1 or 2 decimal places already. There is nothing to cause the program to round. I wonder if it has something to do with trailing zeros getting dropped, e.g., inputting "25.00", pressing enter then returning to the cell to find "25".

Laurent Combe, that works, but it's "0.01", not "0,01". The comma gave me an error code. Thanks for the work-around.

Uwe Auer, your solution also works. Thanks for the work-around.
Comment 6 [REDACTED] 2021-04-12 11:00:47 UTC
(In reply to Chuck from comment #5)

> Uwe Auer, your solution also works. Thanks for the work-around.

Thanks, but it is not meant as a workaround but the way it needs to be done using spreadsheet applications and when doing math with computers using a binary representation of decimal values, which inherently is only an approximation to the decimal value. This binary approximation causes a small difference and when using functions like SUM this difference is added as well and you get error propagation.
Comment 7 Eike Rathke 2021-04-12 12:25:35 UTC
See also https://erack.de/bookmarks/D.html#010203