Issue 126992 - Function ABS returning wrong result
Summary: Function ABS returning wrong result
Status: CLOSED DUPLICATE of issue 69749
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: 4.1.2
Hardware: PC Windows 7
: P5 (lowest) Major (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2016-06-06 18:36 UTC by Luca Boschini
Modified: 2016-06-07 17:21 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.2
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description Luca Boschini 2016-06-06 18:36:24 UTC
In cell A1 put 4000.93
In cell A2 put 4000.94
In cell A3 put =IF(ABS(A1-A2)<=0.01)
You get FALSE while it should be TRUE.

The bug seems to be related to the float representation of numbers because, for instance, using 4000.03 and 4000.04 you get the correct answer (TRUE).

(note: I made tests with Openoffice italian version, using italian formulas - IF=SE, ABS=ASS)
Comment 1 Regina Henschel 2016-06-06 18:50:17 UTC
We have a collective bug for these precision problems. (I know those should all be set to "Blocks" but the others are marked as duplicate too.)

*** This issue has been marked as a duplicate of issue 69749 ***
Comment 2 Andrea Pescetti 2016-06-07 17:21:46 UTC
If it helps, the precision problem is not related to ABS() here as the following formula (here represented in Italian for the user's convenience) is broken too:

=SE(A2-A1 <= 0,01; "TRUE"; "FALSE")

(expected: TRUE; reported: FALSE)

On the other hand, if this helps the user for this specific issue,

=SE(A2 <= A1 + 0,01; "TRUE"; "FALSE")

will give TRUE as expected; so you can probably work around this by rewriting your formula as follows:

=SE(E(A2 <= A1 + 0,01; A1 <= A2 + 0,01); "TRUE"; "FALSE")

The two are equivalent in theory, but decimal numbers in Calc are almost always approximated so this explains the apparent contradiction.