Improve Excel accuracy over 15 digits

[brianpo]

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

 

[Niek Otten]

Excel's precision is 15 decimal digits. See

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

There are add-ins available to calculate with more digits. Be careful: you
can't process those numbers with standard Excel formulas and functions.

Look here:

http://digilander.libero.it/foxes/MultiPrecision.htm
and
http://precisioncalc.com/index.html

But if you just need more digits for any identifiers, such as credit card
numbers or product id's, store them as text, not as numbers

 

[JoeU2004]

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?

The short answer is "no", at least not with the standard product. There are
add-ins available that do multi-precision arithmetic. I know nothing about
them.

Although Excel displays only the first 15 significant digits, arithmetic is
performed to highest degree permitted by the standard binary format that it
uses internally (i.e. IEEE 754). For example, the product of your numbers
results in exactly 12345678987654320. That still does not match hand
calculations; "missed it by t-h-a-t much" ;-) (It is off by only one in
this case.) But it is "correct" insofar as it is consistent with the binary
result that most applications will yield. The next binary representation is
12345678987654322.

 

[Ron Rosenfeld]

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

You could download and install the XNUMBERS addin for EXCEL which performs
multiprecision floating point arithmetic up to 250 significant digits. By
default, it uses the precision of 30 digits.

You can Google for a source. I believe the latest version is 5.6
--ron

 

[Jim Thomlinson]

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.

 

[Dave Peterson]

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

 

[Niek Otten]

<> 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 here:

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.

 

[Dave Peterson]

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]

Exactly!
That's why I plea for Decimal Arithmetic. That's the way we calculate.
And it is not difficult to implement (we teach it to children, don't we?) in
hardware or software. And we implement it in calculators, like your example.
Performance isn't really an issue, I'm sure. We regain all the lost
productivity (because of not understanding how Excel calculates) in no time.

 

[Dana DeLouis]

If it's simple things like this, you can get a text representation of
the answer:

=Times(111111111, 111111111)
=Times(111111111, 111111111, 2)

Returns:
12,345,678,987,654,321.00
24,691,357,975,308,642.00

This is just the very simpler version...

Function Times(ParamArray v())
Dim j As Long
Times = CDec(1)
For j = 0 To UBound(v)
Times = Times * v(j)
Next j
If WorksheetFunction.Log10(Times) > 15 Then
Times = FormatNumber(Times, , , vbTrue)
End If
End Function

= = = = =
HTH :>)
Dana DeLouis

 

[Greg Lovern]

Now that every one else has waded in I will ask the question... Why wouldyou
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.

Hi Jim,

Most of my customers don't use xlPrecison for numbers that represent
any kind of physical measurement. Instead, they are mostly using
numbers to represent abstract, non-physical measurements. Many of them
are in finance.

Others are doing pure mathematics -- mostly number theory, where as
far as I can tell, the number doesn't represent anything other than
itself and the patterns of digits within it.

Others are doing things so unique I'm not sure how to categorize them.
There may be a few delusional cranks among them, but most of them come
across as highly intelligent and educated; certainly not less so than
the chorus of dogmatists who insist there is no valid use for more
than 15 significant digits of precision.

In some cases it's not only about the precision but also about very
large and very small numbers, far outside Excel's numeric range.

Some of them just want exact results (no binary conversion errors)
without dealing with the issues of Precision As Displayed or Round.
Generally those customers are well aware of what the options are.

BTW, here's an example of how precision can be a matter of life and
death:

During the Gulf War, a binary conversion error led to the deaths of 28
American soldiers (and around 100 injured) on February 25th, 1991 when
an American Patriot missile failed to intercept an Iraqi SCUD missile
headed toward their Army barracks. The error was caused by storing
time in 1/10 second increments, with binary conversion errors on
converting 0.1 to binary. Though each conversion error was tiny, the
error accumulated enough to make the Patriot's navigation software
miss the SCUD, which then reached its target.


Also BTW, I'm curious -- how does 15 significant digits of precision
get you the distance from here to the sun to less than a millimeter?
Isn't the average distance approximately 149,600,000,000,000
millimeters, which would let you get to the *nearest* millimeter in 15
significant digits of precision (or within one millimeter, assuming
truncation rather than rounding), but not to a fraction of a
millimeter? Not that it really matters, of course.


Greg Lovern
http://precisioncalc.com

 

[Jerry W. Lewis]

I would like to clarify the false impression that your Patriot Missile
example left. The problem was not binary math per se, but programming that
was not appropriate to the hardware it was intended to run on. Programmers
are expected to know the limitations of the intended hardware and effectively
work around them. This unfortunately did not happen here.

Details of the original incident are summarized at
http://www.ima.umn.edu/~arnold/disasters/patriot.html

The guidance system only performed an external time check when the missile
battery was turned on. It interpreted the current time as the number of
10ths of a second since the system booted, which means that no arithmetic
system would make up for errors in the internal clock. Since knowing the
time was critical to the guidance system, defensive programming would have
periodically verified the time externally. The Dutch Patriot batteries
(using the same flawed software) were much more accurate because they
rebooted frequently, which had the effect of forcing the time to be verified
more often.

Even if the clock was accurate, it is true that the decimal binary
conversion would have an impact in the original algorithm, but the internal
storage format had effectively less than single precision for the
representation of 1/10. Had IEEE double precision (used by Excel) been
employed, the time error (in 10ths of a second) after 100 hours of continuous
operation would have been about 2E-11 instead of 0.3, and thus would have
been completely negligible.

Also, it did not take this tragic miss to uncover the problem, a programming
patch had already been written and shipped; it just arrived one day too late.

Jerry

 

[Bob I]

Hi Jim,

Most of my customers don't use xlPrecison for numbers that represent
any kind of physical measurement. Instead, they are mostly using
numbers to represent abstract, non-physical measurements. Many of them
are in finance.

Others are doing pure mathematics -- mostly number theory, where as
far as I can tell, the number doesn't represent anything other than
itself and the patterns of digits within it.

Others are doing things so unique I'm not sure how to categorize them.
There may be a few delusional cranks among them, but most of them come
across as highly intelligent and educated; certainly not less so than
the chorus of dogmatists who insist there is no valid use for more
than 15 significant digits of precision.

In some cases it's not only about the precision but also about very
large and very small numbers, far outside Excel's numeric range.

Some of them just want exact results (no binary conversion errors)
without dealing with the issues of Precision As Displayed or Round.
Generally those customers are well aware of what the options are.

BTW, here's an example of how precision can be a matter of life and
death:

During the Gulf War, a binary conversion error led to the deaths of 28
American soldiers (and around 100 injured) on February 25th, 1991 when
an American Patriot missile failed to intercept an Iraqi SCUD missile
headed toward their Army barracks. The error was caused by storing
time in 1/10 second increments, with binary conversion errors on
converting 0.1 to binary. Though each conversion error was tiny, the
error accumulated enough to make the Patriot's navigation software
miss the SCUD, which then reached its target.


Also BTW, I'm curious -- how does 15 significant digits of precision
get you the distance from here to the sun to less than a millimeter?
Isn't the average distance approximately 149,600,000,000,000
millimeters, which would let you get to the *nearest* millimeter in 15
significant digits of precision (or within one millimeter, assuming
truncation rather than rounding), but not to a fraction of a
millimeter? Not that it really matters, of course.


Greg Lovern
http://precisioncalc.com

 

[Bob I]

Hi Jim,

Also BTW, I'm curious -- how does 15 significant digits of precision
get you the distance from here to the sun to less than a millimeter?
Isn't the average distance approximately 149,600,000,000,000
millimeters, which would let you get to the *nearest* millimeter in 15
significant digits of precision (or within one millimeter, assuming
truncation rather than rounding), but not to a fraction of a
millimeter? Not that it really matters, of course.

Simple, since 15 significant digits of precision can give you the
distance to the sun to the nearest millimeter the error will be LESS
than one millimeter.