15 digit limit in Excel
15 digit limit in Excel is something everyone should know about. Why? It both enables Excel to do lightning speed calculations and limits Excel in a great way. And the guilty party is IEEE 754.
Whenever you work with large files in Excel, so many worksheets with countless formulas, you have probably wondered, how does Excel do it so fast… Well if it wasn’t for IEEE 754, it would take an eternity. But this cutting of corners or rather of numbers also has a downside. But first things first. What is IEEE 754?
What is IEEE 754 and why can it give you a heart attack in Excel
“Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers. IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers should be stored in a binary computer. It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floating-point units and numeric data processors of nearly all of today’s PC-based microprocessors that implement floating-point math, including the Intel, Motorola, Sun, and MIPS processors.” (Direct quote from http://support.microsoft.com/kb/78113)
What it all means
You will notice this standard in many ways in Excel, but the main is, that if you write an integer with more than 15 digits (which is quite feasible), excel will transform all integers starting with the 16th to zero.
So when you put 1234567890123456789 in a cell, you get 1234567890123450000. The same goes for 1234567890.123456789 that would give 1234567890.123450000! This is by itself quite a drawback, but it doesn’t end there, this limitation impacts all parts of Excel, including calculations. Below is a very simple calculation…
Adding the three numbers in A1, A2 and A3 should result in 0, but you get something else entirely different. This is due to another limitation that is a side effect of the IEEE 754. More precisely, it is a side effect of something that excel did not adopt from IEEE 754 and that is the “negative zero”. So when excel should give something like -0.x as a side result or as the end result, you are in trouble. One daring bystander could even say Excel doesn’t really calculate correctly!
Is there a way around this
The short answer is NO. The long answer is, you can store longer number as text (so begin writing in Excel with an apostrophe), and you will see more than 15 integers, but if you will want to convert them back to numbers and do calculations with them, you will again only work with 15 integers!
The only way to make Excel more precise, is by using an Add-In. There are many out there, here is an example: xlPrecision
Learn more
Check out our YouTube channel and subscribe for more amazing Excel tricks!
Follow us on LinkedIn.
Check out our brand new R Academy!
Comments 21
Leave a Reply
Excel Unplugged acknowledgements
Donate
To avoid a stroke please use the function INTEGER
I’m guessing you are talking about the =INT() function in Excel, which is a great way to get the integer, but does not solve the problem. if you want 1234567890.123456789 INT() will give you 1234567890 whereas Excel will give you 1234567890.123450000 which is still better, but not quite 1234567890.123456789 what we want.
The problem described does not exist… The easiest solution: change format from general to number.
Hi,
if you format the number, so it only shows the whole number or so that it visually rounds the number on 2 decimal places, you will get 0 or 0.00 but that is only what you see. The number in the cell will still be 0.0000000000000001249 (this is from the sample in the post) and if you format it as number it will give you 0.00 whereas general would give you 1.249E-16 but still that is just formatting. You only change what you see, not what is really there. Try formatting the cell as a number but with more than 15 decimal places and you will see the problem. It’s hidden and minute, but still a hassle.
Hello!
A calculus that gives a result with more than 15 digits, 0 (zeros) will appear in the rest of the number. Nonetheless, the calculus is made. For instance, 2^50 = 1,125,899,906,842,624, but EXCEL gives the result 1,125,899,906,842,620. The digit closer to the decimal is “rounded” to 0 (zero).
Exactly, it is made, but it’s wrong. Now some can live with that and some cannot, but it’s better to know what’s happening then to be caught with your pants down 🙂
The following posts are related and may be of interest to readers of this blog:
Rounding Errors in Excel Calculations at http://excelvbacomputing.com/2013/04/
Credit Card Number Validation in Excel at http://excelvbacomputing.com/2013/05/10/
Thanks.
From my previous experience I have learned that it is not required. You can achieve both numbers (rounded and/or exact).
When entering the number 123456789123456789, Excel is automatically set to round the last (3) digits to zeros.
But if you enter the number: _123456789123456789, Excel leaves the number exactly as entered without rounding it off.
If for your calculations) if you put the (_) in front of the number and do a sume of the numbers, you will achieve the exact anwser (0).
Same results as above.
Hi,
you are right, you can put the number in as _123456789123456789, but that is not a number for Excel, it’s a string or text. You do get a sum of 0 but it’s because you are suming text. Try changing one of the numbers, and you will still get 0.
The problem may extends to COUNTIF
Try the following: A list of receipt number is stored/input as text:
‘1234567890123456789
‘1234567890123456790
‘1234567890123456791
‘1234567890123456792
etc…
=COUNTIF(A1:A10,”1234567890123456789″) would give you a result of 10 instead of 1
Hi Gasper,
Are there just certain combinations that create this? I’ve tried, for instance, these numbers: -1.25, +1.14, +.11.
This seems to work correctly, as well as several other combinations. I’m wondering what actually invokes the trigger? If you know.
Thanks for the heads-up.
Donna
You are right, your combination gives 0, but if you change -1.25 into -1.45 and 1.14 into 1.34, it will give you trouble… Excel never seizes to amaze 🙂 And to tell you the truth, I cannot explain this 🙂
Similar to above discussion, my problem is thus:
I want to type two mobile numbers in one cell of the excel with this cell/display format: #####-#####, #####-##### (means one hyphen after every 4 digits).
But it keeps changing the last 4 digits into zeros.
Is it possible in any way?
Hi
I had problems with the 15 character limit for the execel but I could solve using the following tools:
http://precisioncalc.com/xlprecision.html
What is the best and easiest way to test the 3rd decimal digit of a number to determine that the third decimal position is NOT zero.
Example 3.450 should test FALSE
3.876 should test TRUE
I need to know how to do this for a highly specialized rounding issue in a financial calculation.
If your number is in cell A1, then this is your formula
=(ROUNDDOWN(A1,3)-ROUNDDOWN(A1,2))<>0
Good article, I have wondered why this is the case.
As a one-off solution you can manually trim off the first digits in a single cell to expose the rest. For example “=a1/a2” shows result 0.19111111111111. I need a few more digits for a program I use so I then do the calculation “=a1/a2-0.19” which manually trims the digits in front to expose more in back. This can be useful in certain situations.
I want to find exponent of 6 digits numbers eg 157864 but got #Num! error.
Can anyone help on how to solve this?
Thanks
The error you’re getting implies that your data is not numeric and has nothing to do with the Excel 15 digits limit…
A colleague suggested this approach, as I was trying to validate an IBAN number using the MOD function:
Try copying the below in cell B2.
IBAN (after converting to integer): 3214282912345698765432161182
Example using MOD 97: Split the long number:
32142829 36 =MOD(B4,97)
12345698 29 =MOD(CONCAT(C4,B5),97)
7654321 24 =MOD(CONCAT(C5,B6),97)
61182 1 =MOD(CONCAT(C6,B7),97)
Another “issue” with this is when Excel decides to convert a >15-digit text string into exponential format just because it can.
If you open a csv file with an alphanumeric field that has “numeric” strings longer than 15 characters, it will normally display them as exponential and keep them that way if you reformat the column to text. This even happens if you quote the field in the csv.
Solution is to use Data | From Text and force the offending field to Text before importing. How you know in advance to do that, I’m not sure!
I found out when importing stock data with long equipment serial numbers. Annoyingly it is the most critical digits that get vapourised (the righmost ones [UK]).