Description: 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. 2. 3. 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: 7.0.5.2 (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
Created attachment 171085 [details] Calc file
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.
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 ?
Use "=IF(ROUND(B18;2)=ROUND(B39+B59;2);"OK";"No Match")"
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.
(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.
See also https://erack.de/bookmarks/D.html#010203