Excel 2003 Bug

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

if you copy a number like '51234123412349999' into excel, excel treats it as
a number but cannot handle the length and thus stores it in scientific
notation which loses the precision of the original number (it is now
51234123412349900)

this is no bloody use

why does it automatically remove precision just because it thinks you want a
freaking number
 
Hi

Read about this in Excel Help: Getting Started > Microsoft Excel
Specifications > Calculation Specifications > Number precision
 
why does it automatically remove precision just because it thinks you want a
freaking number
Click to expand...

Because it is acting in accord with its specifications, which you can find if
you check HELP for specifications.


--ron
 
51234123412349999 cannot be represented in Excel's numeric storage
format. If you don't need to do math with it (e.g. it is an ID #), then
prefix with a single quote to store it exactly as text.

Almost all software (including Excel) follows the IEEE standard for
double precision storage of numbers. The binary representation defined
there can distinguish every 15 decimal digit number (hence Excel's
documented limit of 15 digits.

Excel does do things a bit oddly when you enter more than 15 digits; it
truncates to 15 digits before conversion to binary, whereas most other
packages would use the trailing digits to fine tune the binary
approximation so that they would display 51234123412350000 (also
different from your input, but slightly closer).

The difference between 51234123412349900 and 51234123412350000 is
0.0000000000002%. If you are doing calculations where you care about
differences that small, then no double precision package would be
adequate. In that case you might look at arbitrary precision packages
such as Maple and Mathematica.

Jerry
 
thanks...

the source data is from a query in sql server 2000...loaded via csv into
excel

i should have thought to add the single quote myself...its not too much
hassle to do (in the query) and works...
 
Back
Top