Apache OpenOffice (AOO) Bugzilla – Issue 124613
IF returning Otherwisevalue when Thenvalue expected
Last modified: 2014-04-09 07:08:10 UTC
Created attachment 83106 [details] The last line is the IF function. =IF(I7=I13;"Balanced";I7-I13) Cells I7 and I13 are apparently equal but the Otherwisevalue is returned and displayed as 0.00. This spreadsheet has not exhibited this problem before. Thus I suspect the data. When the data in the attachment below was first used the displayed value was -0.0 (negative zero). After re-keying some data (with the same values as before), positive zero has been displayed ever afterwards.
Confirmed with AOO410m15(Build:9761) - Rev. 1583666 2014-04-01 13:50 - Linux x86_64 Debian
change format of the result cell to standard and you see that the cells are not equal. you will need to compare rounded values to get the desired result. btw: MS Excel shows same behavior
Reopening
Created attachment 83109 [details] screenshot of spreadsheet A6 and A12 look equal but are not. Why?
Tools - Options... - OpenOffice Calc - Calculate - Precision as shown -> IF still fails.
=A12-A6 Format → Cells…: Numbers: Category "Scientific", Format "-1.23E+003" It isn't a bug. See comment 2.
yes(In reply to Edwin Sharp from comment #4) > A6 and A12 look equal but are not. Why? Yes, odd indeed. I thought it was the usual rounding error with floating point numbers. looks like this is connected to Issue 64851 if you replace A6 =SUM(A1:A4) with =SUM(A1;A2;A3;A4) the result is correct My former notice is incorrect, MS Excel shows correct result (it didn't copy the formula, though) shown difference in AOO is -4,54747350886464E-013 = -0,5^41 "use precision" as shown has no effect on result, see issue 111493 it's not an new issue, already in AOO 3.4.1
(In reply to j.nitschke from comment #7) > it's not an new issue, already in AOO 3.4.1 (In reply to david.cranch@gmail.com from comment #0) > This spreadsheet has not exhibited this problem before. ?
I think, that is is indeed duplicate to issue 64851. The result of addition depends on the order of the arguments. In attached document using A1+A2+A3+A4 yields the shown precision problem, but A1+A2+A4+A3 has no error. The problem is in the addition, not in the IF. Tipp: Never test equality of decimal values using =, but always test with abs( difference ) < epsilon near zero.
(In reply to Regina Henschel from comment #9) > I think, that is is indeed duplicate to issue 64851. The result of addition > depends on the order of the arguments. In attached document using > A1+A2+A3+A4 yields the shown precision problem, but A1+A2+A4+A3 has no > error. The problem is in the addition, not in the IF. if it's a precision problem it should show when you change the cell format to 20 decimal places. as Edwin's screenshot shows, the value is -122.05000000000000000000 for both cells I guess this is a dup of issue 64851 plus an other issue which masks the precision problem.
I am not a math expert, still I know that... - conversion from decimal to floating point representation cannot always be exact - the precision of a Double precision number is equivalent to about 15 or 16 decimal digits in decimal representation - any floating point calculation can add errors. As a consequence : Testing if two floating point numbers are equal is a non-sense! Because of inherent errors you should instead do something like =IF(ABS(A6-A12)<0.0000000001;"Balanced";A6-A12) Further reading : Floating point article in Wikipedia https://en.wikipedia.org/wiki/Floating_point What Every Computer Scientist Should Know About Floating-Point Arithmetic http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html