Subtracting gives the wrong answer!!

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

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.

>-----Original Message-----
>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
>.
>

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

<anonymous@discussions.microsoft.com> wrote in message
news:37e801c4c284$dce9fbd0$a601280a@phx.gbl...
> 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.
>
> >-----Original Message-----
> >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
> >.
> >