Problem with Values greater than a quardrillion(1,000,000,000,000,

  • Thread starter Thread starter tomincnj
  • Start date Start date
T

tomincnj

I use my Excel 2002 Spreadsheet as a import source for my Tagalog Language
Electronic Flashcard Program. Recently while expanding my number proficiency
for random numbers greater than one quadrillion (1,000,000,000,000,000), I
observed the following:

Note all cells were formated to Category = Accounting, Decimal Places = 0,
Symbol = None

1. Any number greater than a guardrillion entered with a unit digit of 1-9
had its unit digit displayed as a zero. ie. 1,000,000,000,000,001 was
displayed as 1,000,000,000,000,000.

2. Entered 1,000,000,000,000,000 in to cell A1. Highlighted cells A1 to A56
then using the feature: Edit/Fill/Series/Columns, Step Value = 1...the
following series was displayed:

Cell A1 to A6 displayed 1,000,000,000,000,000
Cell A7 to A16 displayed 1,000,000,000,000,010
Cell A17 to A26 displayed 1,000,000,000,000,020
Cell A27 to A36 displayed 1,000,000,000,000,030
Cell A37 to A46 displayed 1,000,000,000,000,040
Cell A47 to A56 displayed 1,000,000,000,000,050

This pattern continues.

The Tens, Hundreds, Thousands, place digits are displayed as entered and all
a additions of these place values work correctly.
 
You are running into a limitation on how many digits Excel can store; search
the help file for "Worksheet and workbook specifications and limits" and you
will see: "Number precision 15 digits". The problem is that your numbers are
simply longer numbers than Excel can handle in a cell.

If you are bringing these numbers in from another system, one option would
be to store the number as a string (text) instead of a number, and use text
conversion functions to pull the number back out if/when you need it...
probably using VBA for actual calculations.

HTH,
Keith
 
Back
Top