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
- triangle of numbers,:-( i owe you one
- C program that takes Salesperson details such as Payroll number, Surname, Other Names, Basic Sa
- perfect number
- how to sum the even numbers only
- Addition of two numbers
- how to generate a rondom number btween two ranges
- Is it possible to have limited number of rows and columns in a sheet
- Max number of sheets in workbook?
- Get Column by number
- I would like to fax directly from a fax number in a cell
- Delete everything in a workbook that is a number
- combine number formats
- Subs with a non-predetermined number of arguments?
- Discover number format
- Converting a number to a sentence
- Routine needed for continuing numbers.
- Change every second column to a negative number
- perfect number
- Read integer number
- Display character which appears number of time equal half of maximum time
- sum of series of natural numbers
- prime number
- assembly program that reads an integer number (from 0 to 9).