Hi David!
Sorry to diappoint you but it's not a bug; it's a binary numbers
rounding thing. I'll give you a fuller explanation than normal.
Introduction and Knowledge Base Sources:
Excel is commonly reported as getting its maths all wrong with results
that should be equal being treated as not equal. See the Microsoft
Knowledge base articles for full coverage of difficulties that Excel
and all computing programs have with apparently simple calculations.
It's a Binary Numbers 'thing' and computers all use binary numbers.
You'll find it covered in various Microsoft Knowledge base articles:
78113 XL: Floating-Point Arithmetic May Give Inaccurate Results
http://support.microsoft.com/default.aspx?scid=kb;EN-US;78113
214118 XL: How to Correct Rounding Errors in Floating-Point Arithmetic
http://support.microsoft.com/default.aspx?scid=kb;EN-US;214118
172911 XL: Incorrect Result Raising 10 to Very Large/Very Small Power
http://support.microsoft.com/default.aspx?scid=kb;EN-US;172911
214373 XL2000: Incorrect Result Raising 10 to Very Large/Very Small
Power
http://support.microsoft.com/default.aspx?scid=kb;EN-US;214373
42980 (Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/default.aspx?scid=KB;en-us;42980
Chip Pearson covers the issue at:
http://www.cpearson.com/excel/rounding.htm
Explanation to non-Mathematicians:
Apologies in advance for all crimes against maths committed in an
attempt of one non-maths person trying to explain something in plain
English that ordinary mortals might understand. It's a binary number
"thing".
But to give you an analogy. Here's my brain in action and talking to
myself (as the only way of getting an intelligent conversation going!)
"What's 10 * 2/3?"
Answer:
6.6666666666666666666666666666666666 (get the picture!)
We have to round somewhere so let's use 2 decimal places.
6.67
"What's this answer multiplied by 3/2?"
Answer:
10.005
"But 10 * 2/3 * 3/2 must equal 10 D*&^ it! Anyone knows that:"
10 * 2/3 * 3/2
=10 * (2*3)/(3*2) = 10 * 6/6 = 10 * 1 = 10
"So there must be something wrong with my brain to get this stupid
10.005 answer! I shouldn't have rounded! But I have to somewhere,
otherwise I'll run out of toilet paper. And as soon as I stop, I'll
still get an error! It's just a question of the size of the error!"
The trouble is that some numbers in the decimal system are not exactly
capable of being represented in the binary system used by computers.
Explanation for the more Maths Minded
A slightly edited (To generalise the response only), but very
comprehensive answer to a similar question was posted by Chip
Pearson - Reproduced below in it's entirety:-
----------------------------------------------------------------------
------------------------------
As you have noticed Excel handles only 15 digits of precision. The
reason is that Excel, like many other computer programs, uses the IEEE
(Institute of Electrical and Electronic Engineers) Double Precision
Floating Point number format as the most accurate representation of a
number. You can read more about this at:
www.cpearson.com/excel/rounding.htm ,
But in an oversimplified form, Excel stores numbers as
N = Integer + X*(1/2) + X*(1/4) + X*(1/8) + X*(1/16) +.....+
X*(1/2^51)
Where each X is either 1 or 0. In binary format, there are 51 digits
to the right of the decimal point. In decimal form, 2^51 is about
equal to 10^15, which is why you get approximately 15 digits of
precision. Unless a fractional number can be expressed *exactly* as
the sum of 1/2 + 1/4 + 1/8 + ... + 1/(2^51) it will be stored as an
approximation. This is not unique to computers. Using a finite number
of decimal places, you cannot accurately store the number 1/3.
You can store it as an approximation, like 0.3 or 0.33 or
0.33333333333333 but at some point you're rounding the true value 1/3,
and 0.33333333333...+0.33333333333...+0.33333333333... does NOT equal
1. It equal 0.999999999999...... which is decidedly not 1.
This is a fact of life in computers and in the real world, and in the
realm in which the two coincide.
But what about the rest of the decimal places, and how, if at all, can
I achieve more precision?
You can *display* a number to as many decimal places as you want, but
anything past 15 is no man's land. Within Excel there is no way to
achieve additional precision. Errors in rounding can compound, so that
rounding error in one formula is compounded when the rounded error is
used by other formulas, which themselves round.
Some computer programs use other representations of numbers, but these
programs trade performance and compatibility for precision. Additional
precision comes at the cost of performance and compatibility with
other programs. For example, a program that stored numbers to 100
digits of precision would use a different encoding scheme, and its
data would not be compatible with the majority of computer programs.
The IEEE Double Precision standard provides a universal format that is
"good enough" for the vast majority of uses. Not all, but most. For
good reason, MS chose years ago to use IEEE Doubles for Excel.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
