View previous topic :: View next topic 
Author 
Message 
Tanaka Newbie
Joined: 01 Feb 2005 Posts: 2

Posted: Tue Feb 01, 2005 8:16 am Post subject: CONCATENATE(1.11)=0.0999999999999996 


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 


nom OOo Enthusiast
Joined: 17 Dec 2003 Posts: 153 Location: NSW, Australia

Posted: Tue Feb 01, 2005 8:57 am Post subject: 


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.11 should be equal to 0.1, there should not be any troubles there. When I try =concatenate(n.1n) 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 


carl Super User
Joined: 21 Apr 2003 Posts: 920 Location: Germany

Posted: Wed Feb 02, 2005 12:51 am Post subject: 


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 


Tanaka Newbie
Joined: 01 Feb 2005 Posts: 2

Posted: Wed Feb 02, 2005 7:12 am Post subject: 


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.
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.11)
=CONCATENATE(1.21.1)
The results I got were:
0.1
0.0999999999999999
Changing the second line to
=CONCATENATE(ROUND(1.21.1;2))
did result in 0.1.
And, just to continue the headache...
A5 =1.21.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 


