Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Croc001
 
Posts: n/a
Default 16 digit number wont keep alteration unless format cell to text

Using excel 2000
recieving data from client with 16 numeral characters within cells
cells show an abreviated listing for the number (2.06E+15)
When editing the the number (wanting to change the last number by 1 digit)
i.e.
2059510000000000 - change to - 2059510000000001
this change will not be kept??

I format the cell to "numbers" with no decimal point as a whole number is
required

Any ideas??
Is this number to long for excell 2000??

I'm not a big excel user

Cheers
Croc001

  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

Excel, like most other spreadsheet programs, uses IEEE format for storing
floating point numbers. That format is limited to 15 digits of precision. If
you try to enter a number with 16 digits, the last digit will be lost.

The only way to keep the full 16 digits is to enter the number as text, but
then you can't do arithmetic on it. To increment the final digit, you would
need a VBA macro. That would be fairly simple if you never need to do a
"carry", but quite complicated if the number is, say 2059519999999999.



On Tue, 8 Mar 2005 18:11:01 -0800, "Croc001"
wrote:

Using excel 2000
recieving data from client with 16 numeral characters within cells
cells show an abreviated listing for the number (2.06E+15)
When editing the the number (wanting to change the last number by 1 digit)
i.e.
2059510000000000 - change to - 2059510000000001
this change will not be kept??

I format the cell to "numbers" with no decimal point as a whole number is
required

Any ideas??
Is this number to long for excell 2000??

I'm not a big excel user

Cheers
Croc001


  #3   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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 excactly 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 phonemenon 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

Myrna Larson wrote:

Excel, like most other spreadsheet programs, uses IEEE format for storing
floating point numbers. That format is limited to 15 digits of precision. If
you try to enter a number with 16 digits, the last digit will be lost.

The only way to keep the full 16 digits is to enter the number as text, but
then you can't do arithmetic on it. To increment the final digit, you would
need a VBA macro. That would be fairly simple if you never need to do a
"carry", but quite complicated if the number is, say 2059519999999999.


  #4   Report Post  
 
Posts: n/a
Default

To perform arithmetic on numbers with up to 32,767 significant digits
in Excel, try xlPrecision:

http://precisioncalc.com

More functions coming soon...

Greg


Croc001 wrote:
Using excel 2000
recieving data from client with 16 numeral characters within cells
cells show an abreviated listing for the number (2.06E+15)
When editing the the number (wanting to change the last number by 1

digit)
i.e.
2059510000000000 - change to - 2059510000000001
this change will not be kept??


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I format a cell for a custom part number? PJ Excel Discussion (Misc queries) 4 March 3rd 05 04:57 AM
Telephone number format MarkT Excel Discussion (Misc queries) 6 January 18th 05 11:39 PM
How to format a number in Indian style in Excel? Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 05:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"