Precision of Calculations

[Denise Williams]

I have a question about increasing the precision of
calculations in Excel. I'm trying to develop an Excel
spreadsheet to conduct a specialized kind of analysis, I
have everything running correctly except for one stage of
the analysis.

At one point, the spreadsheet calculates a probability
using the following formula (with input from cell H16)
=1-NORMSDIST(H16) This produces a number in cell H17.

The resulting probability is then entered in the following
formula =TINV(H17,C5-2) to produce a value in cell H18.

Now, if the H16 value is large, then the probability
resulting in H16 is extremely small. The problem is that,
given Excel's default computational precision, Excel will
return the probability as zero when the probability is
very small. This then affects the next step, in which
the probability is entered into the =TINV(H17,C5-2)
formula in cell H18 (where the C5 value is the number of
people in a study, say 250). The resulting H18 value is
then an incorrect 10,000,000 or something along those
lines.

So for example, an H16 value of 13.71 should produce an
H17 probability value of 5/1043 This probability should
then produce an H18 value of 16.59. But Excel's precision
is limited so that the H16 value of 13.71 produces a
probability of 0.000, which produces an H18 value of
10,000,000. this then throws off subsequent analyses.

So, my question is, is there a way to adjust Excel's
default precision of calculation? I think that the
default is 15 sig digits. This is a separate issue from
the number of digits that Excel actually displays in the
spreadsheet.

Any help would be very much appreciated.

 

[Gary Brown]

I think you need to read about Excel's precision
limitations on Chip Pearson's website. Please check out...
http://www.cpearson.com/excel/rounding.htm

HTH,
Gary

 

[Ken Wright]

A compilation of answers to similar posts:-

Excel will round all numbers to 15 significant figures. Anything over and
above this will be rounded to 0. If the data needs to be entered as you
have described, (eg for a credit card number) you need to precede the entry
with an apostrophe or format the cell as text before you enter the data.
You can still do calculations against a number entered as text BUT it will
only use 15 significant figures in the calculation, so that doesn't buy you
anything extra doing it that way.


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, it 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.

Can you recommend a non-Excel app that offers higher precision?

Dedicated mathematical programs like Matlab and Mathamatica can
provide much greater precision, but those results aren't compatible
with most other computer programs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)


--------------------------------------------------------------------------------------------------------

For a calculator that will support more than 15 digits, Jerry W. Lewis has
given the following info and link:-


The decimal data type gives 28 figure if you don't need exponents and
don't mind VBA programing. I think the Windows calculator uses the same
data type.

A free quad precision (64 digit) calculator can be downloaded from
http://www.crbond.com/applications.htm
unless it has been updated, it does not support cut/copy/paste.

I think some extended precision routines using VBA and strings have been
published for Excel - search the Google archives.

In Maple, Mathematica, Matlab, Rexx, etc., you can specify the number of
output figures you want.


Jerry W. Lewis

 

[bobclark]

Try using =max(minimum value, existing formula) to overcome the 0 calculation.
i.e. =max(.001,existing formula). It stinks as far as cqalculations go,BUT, it
overcomes the "not in your great grandchildrens lifetime" zero calculation.

On Wed, 1 Sep 2004 12:39:44 -0700

~>I have a question about increasing the precision of
~>calculations in Excel. I'm trying to develop an Excel
~>spreadsheet to conduct a specialized kind of analysis, I
~>have everything running correctly except for one stage of
~>the analysis.
~>
~>At one point, the spreadsheet calculates a probability
~>using the following formula (with input from cell H16)
~>=1-NORMSDIST(H16) This produces a number in cell H17.
~>
~>The resulting probability is then entered in the following
~>formula =TINV(H17,C5-2) to produce a value in cell H18.
~>
~>Now, if the H16 value is large, then the probability
~>resulting in H16 is extremely small. The problem is that,
~>given Excel's default computational precision, Excel will
~>return the probability as zero when the probability is
~>very small. This then affects the next step, in which
~>the probability is entered into the =TINV(H17,C5-2)
~>formula in cell H18 (where the C5 value is the number of
~>people in a study, say 250). The resulting H18 value is
~>then an incorrect 10,000,000 or something along those
~>lines.
~>
~>So for example, an H16 value of 13.71 should produce an
~>H17 probability value of 5/1043 This probability should
~>then produce an H18 value of 16.59. But Excel's precision
~>is limited so that the H16 value of 13.71 produces a
~>probability of 0.000, which produces an H18 value of
~>10,000,000. this then throws off subsequent analyses.
~>
~>So, my question is, is there a way to adjust Excel's
~>default precision of calculation? I think that the
~>default is 15 sig digits. This is a separate issue from
~>the number of digits that Excel actually displays in the
~>spreadsheet.
~>
~>Any help would be very much appreciated.

 

[Ian Smith]

I have a question about increasing the precision of
calculations in Excel. I'm trying to develop an Excel
spreadsheet to conduct a specialized kind of analysis, I
have everything running correctly except for one stage of
the analysis.

At one point, the spreadsheet calculates a probability
using the following formula (with input from cell H16)
=1-NORMSDIST(H16) This produces a number in cell H17.

The resulting probability is then entered in the following
formula =TINV(H17,C5-2) to produce a value in cell H18.

Now, if the H16 value is large, then the probability
resulting in H16 is extremely small. The problem is that,
given Excel's default computational precision, Excel will
return the probability as zero when the probability is
very small. This then affects the next step, in which
the probability is entered into the =TINV(H17,C5-2)
formula in cell H18 (where the C5 value is the number of
people in a study, say 250). The resulting H18 value is
then an incorrect 10,000,000 or something along those
lines.

So for example, an H16 value of 13.71 should produce an
H17 probability value of 5/1043 This probability should
then produce an H18 value of 16.59. But Excel's precision
is limited so that the H16 value of 13.71 produces a
probability of 0.000, which produces an H18 value of
10,000,000. this then throws off subsequent analyses.

So, my question is, is there a way to adjust Excel's
default precision of calculation? I think that the
default is 15 sig digits. This is a separate issue from
the number of digits that Excel actually displays in the
spreadsheet.

Any help would be very much appreciated.

To calculate these values sensibly, the formula in cell H17 should be
NORMSDIST(-H16). Then you will get vaguely sensible values from Excel
providing the value in H16 is not less than -4. If the value in H16 is
-5 for example, then the value in H17 is 2.871e-7 (relative error
greater than 1e-3 so not very accurate) but TINV does not work at all
sensibly when its first argument is too small.

If you want to calculate the values accurately, you could use the
functions in http://members.aol.com/iandjmsmith/Examples.xls.

H17 should then contain =cdf_normal(-H16) and H18 should contain
=-inv_tdist(H17/2,C5-2). The - and /2 are required because TINV
returns two-tailed values and inv_tdist returns one-tailed values.

By the way an H16 value of 13.71 should give an H17 value of 4.42e-42
not 5/1043 which if the C5 value is 250 leads to an H18 value of
16.879.


Ian Smith