Equality Should be True, But It Isn't

[seanfrisbey]

I have quite a strange issue happening with my spreadsheet. Below is a screenshot of the issue. Column A is the description of the row, column B is the data, column C is the formula for that data. By my understanding, the result in B5 should be TRUE, but it isn't. Can anyone explain why? I have verified that the issue persists even in Excel, so I doubt it is a bug. I must be doing something wrong.

[Villeroy]

Number 1 is not equal to text "1".
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[seanfrisbey]

The cells are all formatted as numbers.

[seanfrisbey]

Minor update...I found that if I truncate the DIFFERENCE to 4 places, it passes. It passes up to 15 decimal places. Once I truncate it to 16 or more, it fails. So, to me, it seems like there is some sort of mathematical thing that should not be present way down in the nitty-gritty of that result...but that is a sloppy workaround. I've got a lot of results I would have to manually truncate to different decimal places.

[Villeroy]

Your screen shot shows left bound values in column B. Usually text is left bound.
Cell formatting has zero effect on the cell values. Text formatted cells can have numbers and numeric cells can have text. You may modify the value but no formatting attribute has any influence on existing values.

ROUND makes decimals comparable.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[seanfrisbey]

So, you are saying that the result of the SUB operation is not a number, and that I have to ROUND it so that it can be considered a number that I can work with?

[seanfrisbey]

And what does Left-Bound mean?

[Villeroy]

Sorry, I mean left aligned. Column C is left aligned which happens automatically when you enter text without specifying the alignment explicitly. Ctrl+F8 disolays numbers in blue, text in black.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[seanfrisbey]

What is green?

[seanfrisbey]

I intended for column C to be left aligned. I wanted to show exactly what I typed in the corresponding cell of column B.

[scsisys]

sean....

Did some experimentation with your sheet info and found that other numbers behave
in the same manner. Uploaded a sample sheet to 4Shared.com ( link below ).

http://www.4shared.com/file/af3-HEtt/equality_true.html

In columns B, C, D & E , change the most right number in rows 2 & 3 to one value less;
for example, change B2 to 15.0175 and B3 to .0175. B5 will return 0 (false). The same
thing happens for the other columns.

Also, change B2 to 16.0175 and B1 to 16.0000. B5 then returns 1 (true). Does the
same for the other columns.

And YES, these are all numbers. Have no idea what the cause is though.

scsisys
_________________
OO 3.2.1
Win XP /SP3

[B Marcelly]

Hi,
This "strange behaviour" is classical. Computations are done in base 2, not base 10. Numbers that you write must be converted to floating point on base 2, and the conversion cannot be exact for all numbers. So what you see is not what is computed.
A consequence is that testing if two floating point numbers are equal is irrelevant.

There is a work-around : Menu Tools > Options > OpenOffice.org Calc > Compute
Check : exactly as displayed.
IMHO this is a bad work-around because the precision of computation will be dependent on the number of decimals displayed.

References :
1 - Wikipedia, see section Representable numbers, conversion and rounding

2 - What Every Computer Scientist Should Know About Floating-Point Arithmetic
_________________
Bernard

OpenOffice.org 1.1.5 fr / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums