 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Entering 18 digit number in Excel
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. > Dear All, > [quoted text clipped - 5 lines] > > 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. > <jaip...@gmail.com> wrote in message > [quoted text clipped - 13 lines] > 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 On May 27, 11:22 pm, jaip...@gmail.com wrote: > 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. > On May 27, 11:22 pm, jaip...@gmail.com wrote: > [quoted text clipped - 12 lines] > 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! On May 27, 11:00 pm, jaip...@gmail.com wrote: > 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 Errata.... On May 27, 11:59 pm, I wrote: > 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) 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++. 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. On May 27, 9:11 pm, jaip...@gmail.com wrote: > 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.) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.