 Search:

# Number crunching

Asked By: Henry    Date: Jan 30    Category: MS Office    Views: 1692

I'm irritated by the ways in which Excel2002 SP3 mangles very large
numbers, both when importing them and when manipulating them.
I receive astronomical event data in .csv files, with one field
representing Unix time, seconds since epoch, measured to 10-
nanosecond resolution, i.e. SSSSSSSSSS.ssssssss. When this data is
imported, it's truncated (i.e. it becomes SSSSSSSSSS.sssss, losing 3
decades of resolution), UNLESS the field is defined as a TEXT field
during importation. Why, oh why would anyone do that, given that the
data is [by definition] comma-delimited? And what on earth goes
wrong when I subtract one value from another to derive elapsed time?
Consider:

A2 = 1147718110.21928262
A3 = 1147718110.21979061

(A3 - A2).VALUE yields 0.00050998
AH, BUT....
right(a3,10)-right(a2,10) yields 0.00050799
and Microsoft's calculator yields 0.00050799 .....

The moral? If you work with large numbers (and they don't have to be
Unix time), be very wary....

Share:

You have hit one of the limitations of Excel. It is explained here
http://support.microsoft.com/kb/kb78113 but essentially it arises from A
floating-point number  is stored in binary in three parts within a 65-bit
range: the sign, the exponent, and the mantissa. .

1 Sign Bit 11 Bit Exponent 1 Implied Bit 52 Bit Mantissa

What this means is that the greatest precision available is expressed by the
52 bit binary mantissa. In decimal terms it means that you can only use 15
digit precision.

If you want to use greater precision than this you need to develop your own
routines for handling the numbers. This is sometimes done by separating the
number at the decimal point. If you do not use arithmetic on the numbers this
is fairly simple (import as text  and then parse at the decimal point) If you
use arithmetic the routines can get complex.

Didn't find what you were looking for? Find more on Number crunching Or get search suggestion and latest updates.