Re: 16 digit number wont keep alteration unless format cell to text

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jerry W. Lewis (post_a_reply_at_no_e-mail.com)
Date: 03/09/05 

Date: Wed, 09 Mar 2005 08:24:39 -0500

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 spread*** 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.

Quantcast