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

CONCATENATE(1.1-1)=0.0999999999999996

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


Joined: 01 Feb 2005
Posts: 2

PostPosted: Tue Feb 01, 2005 8:16 am    Post subject: CONCATENATE(1.1-1)=0.0999999999999996 Reply with quote

Just noticed something odd happening with one of my formulas. I haven't tested all of the possibilities yet, but when I have a formula inside CONCATENATE that results in n.1 - n, the result displayed is 0.0999999999999996 instead of the expected 0.1. Hmm. And n.2 - n.1 gives me 0.100000000000001.

This doesn't seem to be affected by any formatting I put on the cell. Has anyone run into this before? I couldn't find a reference to anything of the sort in the forums. Is there some blindingly obvious solution that I'm just missing? Thanks.

Tommy

Oh yeah. Using OO 1.1.4 on a WinXP Pro system.
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Tue Feb 01, 2005 8:57 am    Post subject: Reply with quote

Yes this is somewhat normal, and has to do with computational rounding and truncation errors. (However I would not expect to see it with such an easy calculation. )

For example 1/3 should be equal to 0.3333333... with an infinite amount of 3s. However if a computer had to keep all this in memory it would literarely have to have an infinite amount of memory (which is impossible). So programmers have to decide where a sensible place to stop.

In this case 1/3 = 0.33333333333333300000
Notice the 5 zeros at the the end. This is "theoretically" wrong, but in practice no one really worries about it. The error is less than 10^-14 !!!

If you are really worried about this you should not use a spreadsheet but a high end mathematical program to do your calculations. All spreadsheets (whether it is OOoor excel or "put a name in here") are the same unless they have a special plugin. (I think someone has written a plugin for excel. I am not sure about OOo)

In any case 1.1-1 should be equal to 0.1, there should not be any troubles there. When I try =concatenate(n.1-n) on OOo 1.1.2 it works as expected. Perhaps it is a bug with 1.1.4?

Can anyone else confirm?
Back to top
View user's profile Send private message
carl
Super User
Super User


Joined: 21 Apr 2003
Posts: 920
Location: Germany

PostPosted: Wed Feb 02, 2005 12:51 am    Post subject: Reply with quote

Quote:
when I have a formula inside CONCATENATE that results in n.1 - n, the result displayed is 0.0999999999999996 instead of the expected 0.1

try putting ROUND in your formula:

=CONCATENATE(round(D13;2);E13)
the 2 is the number of decimal places
_________________
carl
Using OpenOffice.org 2 on XP sp2
Back to top
View user's profile Send private message
Tanaka
Newbie
Newbie


Joined: 01 Feb 2005
Posts: 2

PostPosted: Wed Feb 02, 2005 7:12 am    Post subject: Reply with quote

Quote:
try putting ROUND in your formula:

=CONCATENATE(round(D13;2);E13)
the 2 is the number of decimal places


Curiouser and curiouser. I'd actually tried rounding it yesterday, to no avail. When I try it this morning, however, it works. Confused

Still doesn't explain why the computational error is there in the first place. Just to test it, I created a new spreadsheet, entered two formulas.

=CONCATENATE(1.1-1)
=CONCATENATE(1.2-1.1)

The results I got were:
0.1
0.0999999999999999

Changing the second line to
=CONCATENATE(ROUND(1.2-1.1;2))
did result in 0.1.

And, just to continue the headache...

A5 =1.2-1.1 //result = 0.1
A6 =CONCATENATE(A5;" points") //result = 0.0999999999999999 points
A7 =CONCATENATE(ROUND(A5;2);" points") //result = 0.1 points

Is anyone else using OO 1.1.4 able to duplicate this? Thanks.
Tommy
Back to top
View user's profile Send private message
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