Going Crazy, excel can't subtract!

T

TeddyTash

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?
 
D

David Biddulph

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).
 
M

MartinW

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

HTH
Martin
 
T

TeddyTash

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