You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@openoffice.apache.org by bu...@apache.org on 2014/04/06 03:35:50 UTC
[Issue 124613] New: IF returning Otherwisevalue when Thenvalue
expected
https://issues.apache.org/ooo/show_bug.cgi?id=124613
Issue ID: 124613
Issue Type: DEFECT
Summary: IF returning Otherwisevalue when Thenvalue expected
Product: Calc
Version: 4.0.1
Hardware: PC
OS: Windows Vista
Status: UNCONFIRMED
Severity: normal
Priority: P3
Component: code
Assignee: issues@openoffice.apache.org
Reporter: david.cranch@gmail.com
Created attachment 83106
--> https://issues.apache.org/ooo/attachment.cgi?id=83106&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
--- Comment #4 from Edwin Sharp <el...@apache.org> ---
Created attachment 83109
--> https://issues.apache.org/ooo/attachment.cgi?id=83109&action=edit
screenshot of spreadsheet
A6 and A12 look equal but are not. Why?
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
j.nitschke@ok.de changed:
What |Removed |Added
----------------------------------------------------------------------------
Version|4.0.1 |3.4.1
--- Comment #7 from j.nitschke@ok.de ---
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
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |CONFIRMED
CC| |elish@apache.org
Latest|--- |4.1-dev
Confirmation on| |
Ever confirmed|0 |1
OS|Windows Vista |All
--- Comment #1 from Edwin Sharp <el...@apache.org> ---
Confirmed with
AOO410m15(Build:9761) - Rev. 1583666
2014-04-01 13:50 - Linux x86_64
Debian
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
--- Comment #6 from mroe <mr...@gmx.net> ---
=A12-A6
Format → Cells…:
Numbers: Category "Scientific", Format "-1.23E+003"
It isn't a bug. See comment 2.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
j.nitschke@ok.de changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|CONFIRMED |RESOLVED
CC| |j.nitschke@ok.de
Resolution|--- |NOT_AN_ISSUE
--- Comment #2 from j.nitschke@ok.de ---
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
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
--- Comment #10 from j.nitschke@ok.de ---
(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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
Regina Henschel <rb...@t-online.de> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |rb.henschel@t-online.de
--- Comment #9 from Regina Henschel <rb...@t-online.de> ---
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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|NOT_AN_ISSUE |---
--- Comment #3 from Edwin Sharp <el...@apache.org> ---
Reopening
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
--- Comment #8 from Edwin Sharp <el...@apache.org> ---
(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.
?
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
bmarcelly <ma...@club-internet.fr> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |marcelly@club-internet.fr
--- Comment #11 from bmarcelly <ma...@club-internet.fr> ---
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
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124613] IF returning Otherwisevalue when Thenvalue expected
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124613
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |CONFIRMED
--- Comment #5 from Edwin Sharp <el...@apache.org> ---
Tools - Options... - OpenOffice Calc - Calculate - Precision as shown -> IF
still fails.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.