Adding more than 15 digits in Excel
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.
What are the recommended 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.
Without using ' before the value, is there any other way to keep value same?
Solution 1:
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.
Solution 2:
When you don't need to calculate with these numbers, you can treat them as text, see the other answers. When you need to calculate with them, then it becomes difficult because Excel has only 15 siginificant digits. The possibilities I know are:
Split the number in 2 (or more) cells. Put a part of the number in one cell and the remainder in another cell. The consequence is that you need to develop your own formules for calculations.
Another solution is using add-in's. For a free one study the articles mentioned in http://www.excel-ticker.com/calculation-of-very-large-numbers-in-excel-part-5-add-in/
Solution 3:
The reason is the limited precision that can be stored in a floating point variable. For a complete explanation you should 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 accessible site, is Chip Pearson's site.
In Excel, the 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 the MSDN article Excel Worksheet and Expression Evaluation).