up vote 1 down vote favorite
share [g+] share [fb]

I want to add more than 20 digits in an Excel cell.

The current format of the cell is general, it converts the number to an exponential format.
I tried with a number format and accounting but when I enter more than 15 digits it gets converted to 0's.

Please recommend steps for stopping Excel from converting data to Exponential Format for 20 digits when in the general format.

Example: 12345678901234567890

Excel converts it to 1.23457E+19 in general format.

with out using ' before value is there any other way to keep value same.

link|improve this question
0% accept rate
Do you really need to do calculations with that much precision? That's like trying to count how many hydrogen atoms you could line up along the earth's orbital radius. What practical difference does it make if you're out by a million or two atoms? – Andrew Turner Jan 3 at 13:37
feedback

3 Answers

up vote 5 down vote

Some numbers are displayed in exponential format if the column is too narrow and you can fix this by increasing the width of the column.

However, very large numbers in Excel are stored in floating point format and cannot be represented in Excel exactly. You may be able to override their display using cell formatting but the true values will still be stored and processed using floating point arithmetic, with its inherent limitations.

If you need to perform calculations with greater precision you need an application that supports arbitrary precision arithmetic.

link|improve this answer
By increasing width of column also number format in exponential only – user111921 Jan 3 at 5:04
@user111921, correct. If the number is very large it is stored in floating point format and must be displayed in exponential form, no matter how wide the column is. – Mike Fitzpatrick Jan 3 at 5:14
NUmber format is normal number there is no floating point.For normal number range after 15 digits value the value is changes to exponential format – user111921 Jan 3 at 5:19
@user111921, correct. If you enter a large integer into Excel it will be converted to floating point (with some loss of precision) and displayed in exponential form. It is unavoidable in Excel and most other applications unless they specifically support arbitrary precision arithmetic. – Mike Fitzpatrick Jan 3 at 5:23
feedback
up vote 1 down vote

nThe reason is the limited precission that can be stored in a floating point variable. For a complete explanation you shoud read the paper "What Every Computer Scientist Should Know About Floating-Point Arithmetic", by David Goldberg, published in the March, 1991 issue of Computing Surveys.

Another, more accesable site, is Chip Pearson's site

In Excel floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number. These can display 15 digit precision (well sort of, see this MSDN article)

link|improve this answer
feedback
up vote 0 down vote

I dont have Excel 2003 but on 2007 this works.

Right click on cell -> format cell -> Number tab -> Select category number.

Adjust decimals as you need.

link|improve this answer
This doesn't answer the author's question. This does not change percision of the data stored. Excel will only be accurate to a certain percision. Excel is not design for certain tasks, any task that requires percision outside of Excel's accuracy, means more scientific tools are required. – Ramhound Jan 3 at 14:37
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.