Subtracting gives the wrong answer!!

[Guest]

Hello all,

I'm just doing some general adding, subtracting and some "IF" formulas etc.
in a worksheet. All was going great until I got to one sum and the formula
came back with "incorrect". Now, I've tried this on another colleague's pc
as well - in case I had a wrong setting, and it came up with the same answer.
The sum is the following (try it yourself!)
5012.10-5024.70
The answer should be -12.60, which is correct, but the formula showed
incorrect. It wasn't until I pressed F2 and then F9 I saw the complete
answer - which is -12.5999999999995. I know that it probably just rounded
the answer off somehow, but how did it find the answer?? I'm only using 2
decimal numbers.

Could this be a glitch?? I've tried different numbers which end in 12.10 and
24.70, but it works fine.

I'm just wondering how many other calculations are like this???

Thanks for your time

Aitch

 

[Guest]

hi,
welcome to microsoft products.
I don't know why but in my 15 years(or so) that i have
been using microsoft products(excel and access mostly), i
have found that they do this perticularly in calculations.
the calculated answer should be six but you get
5.999999999999999999999...
a built in math bug of some sort.

 

[Steve Dalton]

It's not a bug. It's to do with the fact that floating point numbers are
stored as truncated binary representations not the truncated decimal
representations that we are accustomed to using in daily life. For example,
0.5 decimal is exactly 0.1 binary, but 0.2 decimal is a recurring decimal in
binary. You have two solutions: use the ROUND function (after adding a very
small increment to your result) or use the "precision as displayed" feature.

Steve Dalton