[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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.
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?
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
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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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