G
Guest
When I try entering a number which has at least 16 digits, the 16th digit and all the digits that follow are replaced by zeros!?! Can anyone give me an explanation and a way to prevent this. Thank you.
K
Ken Wright
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.
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.
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
--------------------------------------------------------------------------------
--------------------------
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
----------------------------------------------------------------------------
explanation and a way to prevent this. Thank you.
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.
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.
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
--------------------------------------------------------------------------------
--------------------------
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
----------------------------------------------------------------------------
all the digits that follow are replaced by zeros!?! Can anyone give me anwifigoo said:
explanation and a way to prevent this. Thank you.
R
RagDyer
XL has 15 digit precision.
If you're numbers are not for calculation (credit card accts.), they can be
entered as "Text".
On a cell by cell basis, precede their entry with an apostrophe ( ' ), which
will *not* be visible in the cell itself, but *will* be displayed in the
formula bar.
On a larger scale, *pre-format* the range of cells for data entry as "Text".
Changing formats *after* data entry doesn't work.
If you really need true numbers in excess of 15 digits, XL is not the
program for you.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
When I try entering a number which has at least 16 digits, the 16th digit
and all the digits that follow are replaced by zeros!?! Can anyone give me
an explanation and a way to prevent this. Thank you.
If you're numbers are not for calculation (credit card accts.), they can be
entered as "Text".
On a cell by cell basis, precede their entry with an apostrophe ( ' ), which
will *not* be visible in the cell itself, but *will* be displayed in the
formula bar.
On a larger scale, *pre-format* the range of cells for data entry as "Text".
Changing formats *after* data entry doesn't work.
If you really need true numbers in excess of 15 digits, XL is not the
program for you.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
When I try entering a number which has at least 16 digits, the 16th digit
and all the digits that follow are replaced by zeros!?! Can anyone give me
an explanation and a way to prevent this. Thank you.
M
Myrna Larson
The limit on number of digits in a number is 15. See Specifications in Help.
If this is a credit card number which is used as though it's text, then
precede your entry with an apostrophe. But you won't be able to do any math on
such cells.
explanation and a way to prevent this. Thank you.
If this is a credit card number which is used as though it's text, then
precede your entry with an apostrophe. But you won't be able to do any math on
such cells.
all the digits that follow are replaced by zeros!?! Can anyone give me an
explanation and a way to prevent this. Thank you.
G
Guest
Ken, Thank you very much for your helpful detailed answer and the links provided. Wish you a nice day. Kind regards, Olivier.
K
Ken Wright
You're welcome, though they are all due to Chip really.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
provided. Wish you a nice day. Kind regards, Olivier.wifigoo said: