Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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.

Related Topics:

- Converting a "Stringed" Number to a Number
- Converting a "Stringed" Number to a Number
- large numbers in java
- How do I validate Phone numbers in java/ email addresses
- Read hardware serial number and execute OS Commands?
- IP numbers
- Help in Random Numbers
- Limit in number of patterns in MessageFormat
- smaller number possible instead of the maximum
- Formatting number
- Getting CPU Number (Id) of a computer using Applet
- How to truncate a floating number?
- convert from number (0-255) to character
- Fibonacci and Prime numbers
- generating random numbers
- Number Format
- Pocket PC 2002 PDA to dail a phone number
- julian day number
- Formating the Number
- Error preallocating sequence numbers. The sequence table information
- How to access the Phone Number from the J2ME Application
- get the number of JList which is created by array
- changing the port number on JBOSS
- Jbuilder Serial number
- Number fields in Excel being considered NULL (empty)