Issue 124613

Summary: IF returning Otherwisevalue when Thenvalue expected
Product: Calc Reporter: david.cranch
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: elish, j.nitschke, marcelly.bernard, rb.henschel
Version: 3.4.1   
Target Milestone: ---   
Hardware: PC   
OS: All   
Issue Type: DEFECT Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---
Attachments:
Description Flags
The last line is the IF function.
none
screenshot of spreadsheet none

Description david.cranch 2014-04-06 01:35:50 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.
Comment 1 Edwin Sharp 2014-04-06 06:30:18 UTC
Confirmed with
AOO410m15(Build:9761)  -  Rev. 1583666
2014-04-01 13:50 - Linux x86_64
Debian
Comment 2 j.nitschke 2014-04-06 09:50:06 UTC
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
Comment 3 Edwin Sharp 2014-04-06 10:01:02 UTC
Reopening
Comment 4 Edwin Sharp 2014-04-06 10:02:25 UTC
Created attachment 83109 [details]
screenshot of spreadsheet

A6 and A12 look equal but are not. Why?
Comment 5 Edwin Sharp 2014-04-06 10:05:07 UTC
Tools - Options... - OpenOffice Calc - Calculate - Precision as shown -> IF still fails.
Comment 6 mroe 2014-04-06 10:38:47 UTC
=A12-A6
Format → Cells…:
Numbers: Category "Scientific", Format "-1.23E+003"

It isn't a bug. See comment 2.
Comment 7 j.nitschke 2014-04-06 10:46:41 UTC
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
Comment 8 Edwin Sharp 2014-04-06 10:52:16 UTC
(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.
?
Comment 9 Regina Henschel 2014-04-06 15:12:02 UTC
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.
Comment 10 j.nitschke 2014-04-06 16:06:06 UTC
(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.
Comment 11 bmarcelly 2014-04-09 07:08:10 UTC
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