Re: Precision of Calculations

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ken Wright (ken.wright_at_NOSPAMntlworld.com)
Date: 09/01/04 

Date: Wed, 1 Sep 2004 21:43:50 +0100

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           chip@cpearson.com
--------------------------------------------------------------------------------------------------------
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
----------------------------------------------------------------------------------------------------------
Hope this helps.
-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Gary Brown" <anonymous@discussions.microsoft.com> wrote in message 
news:029301c4905f$5e4e2910$a401280a@phx.gbl...
>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
>
>
>
>>-----Original Message-----
>>I have a question about increasing the precision of
>>calculations in Excel.  I'm trying to develop an Excel
>>spread*** to conduct a specialized kind of analysis, I
>>have everything running correctly except for one stage of
>>the analysis.
>>
>>At one point, the spread*** 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
>>spread***.
>>
>>Any help would be very much appreciated.
>>
>>.
>>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.747 / Virus Database: 499 - Release Date: 01/09/2004
Quantcast