Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do you display 16 digit numbers in excel ? (it keeps convert.
I'm trying to display 16 digit numbers in excel but it keeps converting the
last digit to a zero. Is there a way to format the cells so the 16 digits are displayed and can be used in a vlookup formula ? |
#2
|
|||
|
|||
On Wed, 30 Mar 2005 01:55:02 -0800, CiaraF
wrote: I'm trying to display 16 digit numbers in excel but it keeps converting the last digit to a zero. Is there a way to format the cells so the 16 digits are displayed and can be used in a vlookup formula ? Excel (and most spreadsheet) specifications are in accord with an IEEE standard which has a 15 digit precision. The only way to do what you want is to enter the value as TEXT. Either precede your entry with an apostrophe (which will not be displayed; OR format the cell as TEXT prior to making your entry. VLOOKUP will work with TEXT entries. --ron |
#3
|
|||
|
|||
Ron Rosenfeld wrote:
.... Excel (and most spreadsheet) specifications are in accord with an IEEE standard which has a 15 digit precision. .... FYI Many IEEE double precision packages will display more than 15 digits (for instance, R and S-PLUS display 17). Most 16 digit numbers, including integers up to 2^53-1 = 9007199254740991 are exactly representable. Presumably MS chose to limit display to 15 digits to avoid questions when unrepresentable 16 digit numbers, such as 2^53+1 = 9007199254740993 get changed to different 16 digit numbers (9007199254740992) that are representable. This also relates to a difference between Excel and VBA. If you past a 16+ digit number into Excel, the number will be truncated (not rounded) to 15 digits before conversion to binary. If you paste a 16+ digit number into into VBA, that number will be converted to binary directly (the trailing digits will impact the binary representation), although subsequent editing of that line would then obliterate the original extra precision. The easiest way to see this phenomenon is to compare the result of the Excel formula =("2059510000000001"-"2059510000000000") with the result of the VBA formula dif = CDbl("2059510000000001") - CDbl("2059510000000000") You can use VBA to poke better representations of 16+ digit numbers into Excel cells. It would be nice if future versions of Excel would do the binary conversion without truncation (like VBA), but I'm not holding my breath ... Jerry |
#4
|
|||
|
|||
On Wed, 30 Mar 2005 08:02:02 -0500, "Jerry W. Lewis"
wrote: Ron Rosenfeld wrote: ... Excel (and most spreadsheet) specifications are in accord with an IEEE standard which has a 15 digit precision. ... FYI Many IEEE double precision packages will display more than 15 digits (for instance, R and S-PLUS display 17). Most 16 digit numbers, including integers up to 2^53-1 = 9007199254740991 are exactly representable. Presumably MS chose to limit display to 15 digits to avoid questions when unrepresentable 16 digit numbers, such as 2^53+1 = 9007199254740993 get changed to different 16 digit numbers (9007199254740992) that are representable. This also relates to a difference between Excel and VBA. If you past a 16+ digit number into Excel, the number will be truncated (not rounded) to 15 digits before conversion to binary. If you paste a 16+ digit number into into VBA, that number will be converted to binary directly (the trailing digits will impact the binary representation), although subsequent editing of that line would then obliterate the original extra precision. The easiest way to see this phenomenon is to compare the result of the Excel formula =("2059510000000001"-"2059510000000000") with the result of the VBA formula dif = CDbl("2059510000000001") - CDbl("2059510000000000") You can use VBA to poke better representations of 16+ digit numbers into Excel cells. It would be nice if future versions of Excel would do the binary conversion without truncation (like VBA), but I'm not holding my breath ... Jerry Thank you for that explanation. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dont display the colors on the Cell ( Excel 2003 ) | New Users to Excel | |||
Exporting numbers into Excel | Excel Discussion (Misc queries) | |||
Excel: To Display Formula in the cells Instead of Value | Excel Discussion (Misc queries) | |||
Excel should be able to compute the MOD of large numbers. | Excel Worksheet Functions | |||
column headings display as numbers | Setting up and Configuration of Excel |