See the recommendations towards the end of the following note, they may help:-
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
