Calculation Error?

  • Thread starter Wei Ming from singapore
  • Start date
W

Wei Ming from singapore

Sometime the calculation is not accurate, Eg:
11.12 - 10.5 = 0.62

but i get 0.619999999999999

Is it system error or regional setting's problem~~~~
 
J

joeu2004

Wei said:
Sometime the calculation is not accurate, Eg:
11.12 - 10.5 = 0.62
but i get 0.619999999999999
Is it system error or regional setting's problem
Click to expand...

Neither. The problem is two-fold:

1. The displayed values are probably rounded, if they are the result of
a formula. For example, "11.12" might be any value between 11.115 and
11.1249...9. You can confirm this by formatting Number with many
decimal places (Format > Cells > Number).

2. Binary computers cannot represent most fractions accurately. So
even if you type "11.12" into a cell and Excel displays "11.120...0",
the value in the computer is not exactly 11.12. Excel makes
adjustments when evaluating some formulas in an attempt to hide these
anomalies. But it is not unusual for the "inaccurate" representation
to have visible consequences.

You can usually compensate by using ROUND() judiciously.
 
J

Jerry W. Lewis

Specifically, the IEEE double precision binary approximation to 11.12 is
11.1199999999999992184029906638897955417633056640625
which you can't see directly, because (as documented) Excel displays no more
than 15 significant figures. But you can see the approximation indirectly,
since when you subtract 10.5 (exactly representable), you get
6.199999999999992184029906638897955417633056640625
which Excel displays to 15 figures as
6.19999999999999

Jerry
 
Top