OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Equality Should be True, But It Isn't

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 9:39 am    Post subject: Equality Should be True, But It Isn't Reply with quote

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.

Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 11, 2012 10:11 am    Post subject: Reply with quote

Number 1 is not equal to text "1".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 10:12 am    Post subject: Reply with quote

The cells are all formatted as numbers.
Back to top
View user's profile Send private message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 10:23 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 11, 2012 12:01 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 12:14 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 12:15 pm    Post subject: Reply with quote

And what does Left-Bound mean?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 11, 2012 12:34 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 1:01 pm    Post subject: Reply with quote

What is green?
Back to top
View user's profile Send private message
seanfrisbey
General User
General User


Joined: 11 May 2012
Posts: 7

PostPosted: Fri May 11, 2012 1:03 pm    Post subject: Reply with quote

I intended for column C to be left aligned. I wanted to show exactly what I typed in the corresponding cell of column B.
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Fri May 11, 2012 3:31 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Fri May 11, 2012 10:55 pm    Post subject: Reply with quote

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 / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group