calculation accuracy

[flavio.c]

Hi friends,

I have a simple question about calculation accuracy (excel 2002) that drives
me crazy: just enter in
A1 value 388
B1 value 372.58
C1 value 15.42

and then in a new cell the formula = A1 - B1 - C1

One would expect 0 as result, instead gets 1.59872E-14

Do you know why this occur? There is a way to force the result to 0 anyway?

Thanks a lot for any help or suggestion on this.

Ciao

Flavio

 

[David Biddulph]

Try to work out the exact binary representation for 372.58 and that will
give you a clue as to the reason. [Hint: there isn't an exact binary
representation for most decimal numbers, just as there isn't an exact
decimal representation for a number like =10/3. You're OK with 0.5 or 0.25,
but with 0.1 or 0.01]

Try =ROUND(A1-B1-C1,2) or rounding to whatever resolution you need.

More info at http://www.mcgimpsey.com/excel/pennyoff.html

 

[Niek Otten]

Hi David,

<More info at http://www.mcgimpsey.com/excel/pennyoff.html>

Actually, that is another subject; the difference between what is displayed and what is used in calculations.

The problem of the OP is described here:

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message | Try to work out the exact binary representation for 372.58 and that will
| give you a clue as to the reason. [Hint: there isn't an exact binary
| representation for most decimal numbers, just as there isn't an exact
| decimal representation for a number like =10/3. You're OK with 0.5 or 0.25,
| but with 0.1 or 0.01]
|
| Try =ROUND(A1-B1-C1,2) or rounding to whatever resolution you need.
|
| More info at http://www.mcgimpsey.com/excel/pennyoff.html
| --
| David Biddulph
|
| | > Hi friends,
| >
| > I have a simple question about calculation accuracy (excel 2002) that
| > drives me crazy: just enter in
| > A1 value 388
| > B1 value 372.58
| > C1 value 15.42
| >
| > and then in a new cell the formula = A1 - B1 - C1
| >
| > One would expect 0 as result, instead gets 1.59872E-14
| >
| > Do you know why this occur? There is a way to force the result to 0
| > anyway?
| >
| > Thanks a lot for any help or suggestion on this.
| >
| > Ciao
| >
| > Flavio
| >
|
|

 

[frabbio]

Thank you Niek, it is clear now.
I think I'll use 'Set precision as displayed', it works fine.
Ciao

Flavio