Odd Concatenate problem.

[Paul]

Running XP Pro with Office 2003 pro upto SP2.

=CONCATENATE(C6-D6," ans")

Where C6 is 8.2 and D6 is 8.1
The answer I get is 0.0999999999999996 ans. Extending the range of numbers
I get: -
1.2 1.1 0.0999999999999999 ans
2.2 2.1 0.1 ans
3.2 3.1 0.1 ans
4.2 4.1 0.100000000000001 ans
5.2 5.1 0.100000000000001 ans
6.2 6.1 0.100000000000001 ans
7.2 7.1 0.100000000000001 ans
8.2 8.1 0.0999999999999996 ans
9.2 9.1 0.0999999999999996 ans
10.2 10.1 0.0999999999999996 ans
11.2 11.1 0.0999999999999996 ans
12.2 12.1 0.0999999999999996 ans
13.2 13.1 0.0999999999999996 ans
14.2 14.1 0.0999999999999996 ans
15.2 15.1 0.0999999999999996 ans
16.2 16.1 0.0999999999999979 ans
17.2 17.1 0.0999999999999979 ans
18.2 18.1 0.0999999999999979 ans
19.2 19.1 0.0999999999999979 ans
20.2 20.1 0.0999999999999979 ans
21.2 21.1 0.0999999999999979 ans
22.2 22.1 0.0999999999999979 ans
23.2 23.1 0.0999999999999979 ans
24.2 24.1 0.0999999999999979 ans
25.2 25.1 0.0999999999999979 ans
26.2 26.1 0.0999999999999979 ans
27.2 27.1 0.0999999999999979 ans
28.2 28.1 0.0999999999999979 ans
29.2 29.1 0.0999999999999979 ans
30.2 30.1 0.0999999999999979 ans
31.2 31.1 0.0999999999999979 ans
32.2 32.1 0.100000000000001 ans
33.2 33.1 0.100000000000001 ans
34.2 34.1 0.100000000000001 ans
35.2 35.1 0.100000000000001 ans
36.2 36.1 0.100000000000001 ans

This is very odd. Any Ideas? Only two of the above are correct, but of
couse all of them should read 0.1

 

[Gary''s Student]

This is a simple rounding issue. Use the ROUND() function if you only want
one decimal place.

 

[Paul]

Thanks Gary, but 8.2 less 8.1 is 0.1 in any mathematics. No rounding required.
I'm familiar with the rounding issues in concatenate, but this is a weird
problem.

 

[TRYING]

So how do you include ROUND in the formula to achieve what Paul is expecting?

 

[TRYING]

And what makes the second and third pairs different from all the rest?

 

[Paul]

Yes true, to include Round, you have to work out the result in a Feeder Cell,
and use that feeder cell in the Concatenate formula. But Round is not the
issue here.
This error only occurs with a subtraction, and only when decimal places are
used.
Have you got the same error...or is it my Excel that's bugged?

 

[TRYING]

I got the same error. I extended downward to about 50 rows with the same
results. I'm using 2003.

 

[Paul]

Oh good....or rather not good.....but it's a MS error and not mine!!

Who's got a direct contact at MS?.....I've tried Emailing problems
before...never an answer.

 

[Bernard Liengme]

The 'rounding' that is referred to here is the rounding that happens when a
computer application has to convert decimal values (numbers to base 10 - the
number of fingers we humans have) to numbers to base 2 (the number of states
a memory position has - on/off or 0/1 depending how you want to think)
For more read some of these
INFO: Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/defaul...pport/kb/articles/Q42/9/80.ASP&NoWebContent=1
http://support.microsoft.com/kb/78113/en-us
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
best wishes

 

[T. Valko]

This is indeed a rounding issue! See the links in Bernard's response.

Try this:

=ROUND(C6-D6,1)&" ans"