Entering 18 digit number in Excel

[jaipal1]

Dear All,

I am trying to enter a 18 digit number in one cell. Surprisingly, the
lasst two digits are automatically being changed to 00.

The number i tried to enter is - 857013340418093014. The last two
digits i.e. 14 is automatically changed to 00!

Could any one of you please help.

Thanks & Regards,
Jaipal Singal.

 

[Howard Kaikow]

Dear All,

I am trying to enter a 18 digit number in one cell. Surprisingly, the
lasst two digits are automatically being changed to 00.

The number i tried to enter is - 857013340418093014. The last two
digits i.e. 14 is automatically changed to 00!

Could any one of you please help.

Excel, like all applications, has to choose an internal data type in which
to store numbers.
Your number has too many significant digits to be accurately stored.

 

[jaipal1]

Excel, like all applications, has to choose an internal data type in which
to store numbers.
Your number has too many significant digits to be accurately stored.

I am sorry - I did not understand what you mean by - "too many
significant digits" & "internal data type". I thought the data type is
number & at least 18 digits should be generally allowed

 

[joeu2004]

I am trying to enter a 18 digit number in one cell. Surprisingly,
the lasst two digits are automatically being changed to 00.

Actually, the last 3 digits. Excel data entry is limited to 15
significant digits. By coincidence, your 16th digit is already zero.

The number i tried to enter is - 857013340418093014.

If you do not need to use the number for computation, you can enter it
as text by prefixing the number with an apostrophe; that is,
'857013340418093014.

If you need a computable number, the best you can do is:

=857013340*10^9 + 418093014

However, because of the limitations of the Excel internal
representation -- which is typical of most binary computer
applications -- that will result in the value 857013340418093056 -- 28
more than your number.

(The closest number less than that is 857013340418092928, which 86
less than your number.)

 

[joeu2004]

I thought the data type is number & at least 18 digits
should be generally allowed

Whatever gave you that idea? That is not a provocative comment. I am
genuinely interested in where you found that misinformation.

In Excel 2003, if you use Help to search for "limits", click on "Excel
specifications and limits", then click on "Calculations
specifications", you will see that the "number precision" is 15
digits.

Although Excel will enforce that limit on data entry and data display,
the internal representation is capable of representing __some__
decimal numbers with more digits.

 

[jaipal1]

Whatever gave you that idea?  That is not a provocative comment.  I am
genuinely interested in where you found that misinformation.

In Excel 2003, if you use Help to search for "limits", click on "Excel
specifications and limits", then click on "Calculations
specifications", you will see that the "number precision" is 15
digits.

Although Excel will enforce that limit on data entry and data display,
the internal representation is capable of representing __some__
decimal numbers with more digits.

Thanks a lot for your help & information. As i had already mentioned
in my earlier post, i just thought that 18 digits should be allowed.
However, as you have correctly specified, I also checked in help for
limits and got the same answer as posted by you. Thanks again!

 

[joeu2004]

i just thought that 18 digits should be allowed.
However, as you have correctly specified, I also
checked in help for limits and got the same answer
as posted by you. Thanks again!

You're welcome. In your defense, I did find a Microsoft web page [1]
that claims that a VB type Double "can hold as many as 18 significant
digits".

That is just plain wrong. It can represent "as many as" 1074
significant digits in __some__ cases (namely, just one: the most
fractional digits with no non-fractional non-zero digits). But VB
Double uses the same internal representation as an Excel number. The
largest integer that is not a power of 2 and that can be represented
exactly is 2^53 - 1, a 16-digit number.

(But Excel will display only the first 15 digits.)


Endnotes:

[1] http://msdn.microsoft.com/en-us/library/5c53yzyb.aspx

 

[Howard Kaikow]

I am sorry - I did not understand what you mean by - "too many
significant digits" & "internal data type". I thought the data type is
number & at least 18 digits should be generally allowed

The number of digits that may be used is, in general, determined by the
hardware data types, in this case the Double data type.

In addition, there are data types not tied to the hardware, e.g,, the
Currency type, wfich would handle your numbers.

However, numbers are stored using the Double type, largely fo reasons of
speed.

 

[joeu2004]

Errata....

 The largest integer that is not a power of 2 and
that can be represented exactly is 2^53 - 1, a
16-digit number.

Obviously I misspoke, having just demonstrated how to represent an 18-
digit integer. The largest integer that can be presented is a 309-
digit number [1]. But these are special cases.


[1] =2*(2^1023 - 2^970)

 

[Howard Kaikow]

For example, see the book 'The Definitive Duide to How computers Do Math",
ISBN 0-471-73278-8.

And see MSFT KB article 42980.

The number of significant digits is limited by the hardware for the Double
type
Excel, as with most ap0plications, just uses the data types provided by the
underlying programming language, in this case, I expect largely C++.