| 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.1-1)=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.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 |
|
|
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.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 |
|
|
|
