Have the cells formatted to Number (15 dec points)


A1: 159.810000000000000
A2: 159.800000000000000
A3: =A1-A2

A3 returns: 0.009999999999991

Should it not retun 0.010000000000000


What's going on?

The problem is that neither 159.81 nor 159.80 can be precisely represented
in binary. Binary can represent 0.5, 0.25, 0.125, 0.0625, etc., and their
multiples, but you can't represent 159.80 or 159.81 precisely in binary
(just as you can't represent 1/3 precisely in decimal).
--
David Biddulph

"TeddyTash" wrote in message
...

Have the cells formatted to Number (15 dec points)


A1: 159.810000000000000
A2: 159.800000000000000
A3: =A1-A2

A3 returns: 0.009999999999991

Should it not retun 0.010000000000000


What's going on?




--
TeddyTash

Excel is only accurate to 15 Significant Digits, not 15 decimal places.
Decimal points count as one of your Significant Digits.

HTH
Martin

As David Biddulph pointed out, the issue is unavoidable (binary)
approximations to your input values. Specifically, the math problem that
Excel perfomed was

159.81000000000000227373675443232059478759765625
-159.80000000000001136868377216160297393798828125
-------------------------------------------------
0.00999999999999090505298227071762084960937500

Check the math, it is exactly correct (I am not aware of any Excel errors in
basic arithmetic) given the initial approximations. These approximations
differed from your intended values at the 17 and 18th figures, so you
happened to choose values with much better approximations than you might have
since the approximations to some numbers will differ in the 16th figure
(documented in Help for "Worksheet and workbook specifications" subtopic
"Calculation specifications"). You can use that to predict where problems
will occur if you think of your calculation as

159.810000000000??
-159.800000000000??
-------------------
0.010000000000??

The issues of finite precision and binary approximations have been standard
for over half a century in computer numerical calculations--it is not unique
to Excel, which (along with almost all other numerical software packages)
follows the IEEE standard for double precision representation of numbers.

For addition/subtraction of numbers with no more than 2 decimal places, you
could round the final result to 2 or more decimal places without doing
violence to the calculation.

If you want to learn more about the actual representation of numbers in
Excel, you might find the VBA functions at
http://groups.google.com/group/micro...06871cf92f8465
to be useful.

Jerry

"TeddyTash" wrote:


Have the cells formatted to Number (15 dec points)


A1: 159.810000000000000
A2: 159.800000000000000
A3: =A1-A2

A3 returns: 0.009999999999991

Should it not retun 0.010000000000000


What's going on?




--
TeddyTash

Thanks for explaining this guys, the only reason I even noticed the problem in the first place is because I was using the =CONCATENATE function (to put the result of the sum together with some other information), so it looked extremely odd when I seen a long line of numbers rather that 0.01