I remember trying to explain to someone why their average golf scores (with
handicap??) that were calculated in excel didn't match what they got with their
$2 calculator.

I kept getting a "yeah, but my calculator can do it!" response.

Niek Otten wrote:

< And if you really cared about this kind of accuracy, why would you be
using excel--a general purpose spreadsheet program???

Well, sometimes people just don't know. There was a time I didn't know about
the "limited" precision.
I'm in the financial business and I've never found span of precision a
problem. But I did have problems with the fact that some numbers can not be
represented exactly. If you round to (for example) a whole number, two
calculation methods can lead to two totally different results. For example
one calulation method results in 15.4444444444449, the other one yields
15.5000000000001. Then one rounds to 15, the other one rounds to 16.

But there are sometimes unexpected effects. Take the example of the distance
to the sun. What if it were not the distance but the angle you had to fire a
rocket?

For some interesting examples, look he

http://www.eecs.berkeley.edu/~wkahan/Mind1ess.pdf

In the newsgroups, two behaviors if Excel are often confused. One is that
the number shown may not be the actual value (the "penny-off" problem), the
other is the fact that not all numbers can be represented exactly.
That last group is often confused because binary representation and
floating-point computation are often not treated as separate issues.

In fact, binary representations can mimic all decimal calculations, as long
as you don't try to save on the number of bits. Good examples, sometimes
even in hardware, can be found in BCD (Binary Coded Decimal)
representaions, but that is certainly not the only method.
Even in VBA, (so close to Excel!), you can find data types like Currency.

I keep calling for decimal arithmatic in Excel, as an option.
A very large percentage of the questions in the newgroups have to do with
the fact that Excel does not calculate the way its users do.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dave Peterson" wrote in message
...
And if you really cared about this kind of accuracy, why would you be
using
excel--a general purpose spreadsheet program???

Jim Thomlinson wrote:

Now that every one else has waded in I will ask the question... Why would
you
need more than 15 digits of accuracy? With that many digits I can get the
distance from here to the sun to less than a millimeter. Just curious.
--
HTH...

Jim Thomlinson

"brianpo" wrote:

Multiplying 111111111 by 111111111 in Excel 2007 results in
12345678987654300, which is obviously wrong.
Is there a way to improve the accuracy of results over 15 digits?
Thanks, Brian


--

Dave Peterson

--

Dave Peterson