Math Error in Excel???

  • Thread starter Thread starter Erin Kotch
  • Start date Start date
E

Erin Kotch

I have the following values in cells B3 and C3 repectively:

3986.8 & 3912.0 (this is not just formatted to 1 decimal,
these are the actual values in the formula bar)

When I subtract these (=B3-C3) I get 74.8000000000002
(after I pastespecial values and look in the formula bar).

I have corrected it with the ROUND function, but can
anyone tell me WHY I get this weird number? Is there a
setting or something I can change to fix it everywhere?
(this is just one of many that I found on my sheet!)

Thank you!
Erin
 
Erin,

Most numbers cannot be stored *exactly* in binary representation,
just as 1/3 cannot be expressed *exactly* in decimal notation.
Both systems use approximations (e.g., 0.333333333333333 is just
an approximation of the actual, mathematical, value 1/3).

Excel, like nearly all other computer programs, uses what is
called "double precision floating point" numbers, which provide
15 digits of accuracy. What you are seeing is Excel's
approximation of the result, since the actual (mathematical)
result cannot be stored accurately.

For more information, see www.cpearson.com/excel/rounding.htm .

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
As the previous responder's have noted, this is a binary thing. Excel
(and almost all other computer software) uses the IEEE standard for
binary double precision representation of numbers. While 3912 can be
exactly represented, 3986.8 cannot, and must be approximated. When you
use approximate inputs your output will naturally be an approximation.

The IEEE approximation to 3986.8 is 4383532957617357/1099511627776, so
the final answer should be 82243469757645/1099511627776 which in decimal
is 74.8000000000001818989403545856475830078125. Excel correctly
represents that answer to 15 figures (see Help for "Excel specifications
and limits" subtopic "Calculation specifications")

Without worrying about binary conversions, a helpful way to think about
it is using the fact that the binary precision is approximately 15
digits. Thus your problem is
3986.80000000000??...
-3912.00000000000??...
-------------------
74.80000000000??...
which is entirely consistent with Excel's answer of
74.8000000000002

That gives you a way to predict at where you can expect to see
approximation issues crop up. You can then round the result to that
level of precision, or do integer calculations.

Jerry
 
Back
Top