<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

15 digit limit in Excel

Published on
5,654 Points
1,954 Views
7 Endorsements
Last Modified:
This is something everyone should know about Excel since it both enables Excel to do lightning speed calculations and limits Excel in a great way. And the guilty party is IEEE 754.

Pic1.png


What is IEEE 754 and why does it give you a heart attack in Excel

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…

“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)

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…
Pic2.pngAdding 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 or more precisely 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. With this in mind it can be said, that Excel doesn’t really calculate "correctly" (according to people's expectations of what the results should be, if you know about the way calculations are done in Excel, then you know that Excel calculates exactly as it should, but for 95% of people who don’t know this, the calculation is simply incorrect), and if we would like to continue our journey to eternal happiness in Excel, this is good to know!

Is there a way around this

The short answer is NO since Excel works as it was built to work. 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!

Quite a few answers were also given here in the comments section. A few VBA functions are presented that solve the problem and give you an ability to work in Excel without having to keep an eye out.

Now whereas Excel cannot do this, you can get around this by using an Add-In. There are many out there, here is an example: xlPrecision
 
7
Comment
12 Comments
LVL 48

Expert Comment

by:aikimark
This article would have been helpful if you had gone into any detail about our work-around options.
0
LVL 20

Expert Comment

by:ElrondCT
You refer to the standard as IEEE 745, but the quote from the Microsoft page refers to 754. It looks like 754 is the correct number (double-checking both the MS page and Wikipedia).
0
LVL 4

Expert Comment

by:Jorgen
There might be errors in the article - I do not know. But for the ordinary Excel user, it points out some issues, that I have not seen explained beforehand. I therefore was pleased to get the information. Any errors can be corrected, and of course it would be great with an article that described the workaround options in details.
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

LVL 10

Author Comment

by:Gašper Kamenšek
@ElrondCT

Thanks for catching that, looks like I’m getting dyslectic  :)

I have corrected the article.
0
LVL 48

Expert Comment

by:aikimark
You should use the quote tag for the Microsoft verbiage.

Under what circumstances have you had problems with double precision floating point number representation or math?
0
LVL 48

Expert Comment

by:aikimark
it can be said, that Excel doesn’t really calculate correctly
That is a false and misleading statement.  Excel calculates according to the rules of floating point arithmetic.
0
LVL 20

Expert Comment

by:ElrondCT
While Excel may be calculating according to the rules of floating point arithmetic, I think it's appropriate to say it's not really calculating correctly if -1.23 + 1.11 + .12 returns something other than zero. The need to stick Round() functions all over the place is one of the more significant annoyances of Excel. (It might have been nice if the article had mentioned Round() as a method of dealing with some of the precision issues.)
0
LVL 48

Expert Comment

by:aikimark
it's not really calculating correctly
Numbers are stored as binary values.  Just because you are typing them as base10 values, doesn't mean they are stored in a base10 representation.

Without any special libraries, you can do math in VBA code with 28-29 digit precision, depending on the value of the number.  Formulas and numeric cell values are limited to double-precision representation limits.

In the following example, I use the decimal data type for the math and the bad behavior doesn't appear:
x=cdec("-1.23")
x=x + 1.12 + .11
?x
 0 

Open in new window


In this example, I'm showing the capability of Microsoft's decimal data type to store a 29 decimal digit value without error.
x=cdec("12345678901234567890123456789")
?x
 12345678901234567890123456789 
?len(x)
 29 

Open in new window

0
LVL 20

Expert Comment

by:ElrondCT
Yes, I'm well aware of the inherent limitations of representing decimal numbers in binary (I've been involved with computers almost as long as you ;-) ); .2, for instance, can't be represented exactly in binary (any more than 1/3 can be represented exactly in decimal). Since I do a lot of programming of accounting systems, I use the Decimal type heavily. Unfortunately, Excel doesn't have a Decimal type; floating-point calculations are faster, but you have to be aware of where they can bite you, and respond accordingly, because sometimes unadjusted floating-point calculations do not return the correct decimal results.
0
LVL 48

Expert Comment

by:aikimark
@ElrondCT

Just adding two functions, similar to the ones below, should go a long way to solving precision problems.  
Option Explicit

Public Function Dec2Cur(ByVal parmRngOrValue) As Currency
    Static rngArea As Range
    Static rng As Range
    Static decThing As Currency
    If TypeName(parmRngOrValue) = "Range" Then
        decThing = CCur(0)
        For Each rngArea In parmRngOrValue.Areas
            For Each rng In rngArea.Cells
                decThing = decThing + Val(rng.Value)
            Next
        Next
        Dec2Cur = decThing
    Else
        Dec2Cur = CCur(parmRngOrValue)
    End If
End Function

Public Function Dec2Dec(ByVal parmRngOrValue) As Variant
    Static rngArea As Range
    Static rng As Range
    Static decThing As Variant
    If TypeName(parmRngOrValue) = "Range" Then
        decThing = CDec("0")
        For Each rngArea In parmRngOrValue.Areas
            For Each rng In rngArea.Cells
                decThing = decThing + Val(rng.Value)
            Next
        Next
        Dec2Dec = decThing
    Else
        Dec2Dec = CDec(parmRngOrValue)
    End If
End Function

Open in new window

Note: If you pass an area (non-contiguous cells or multiple ranges), you will need surround your formula within another pair of parentheses.
Example:
=Dec2Dec((B1:B3,C1:C3,F4,I7,F14:G17))

Open in new window

0
LVL 20

Expert Comment

by:ElrondCT
Interesting, aikimark. Is this VBA or VB6? (It's clearly not VB .NET, which is my primary language currently.)
0
LVL 48

Expert Comment

by:aikimark
It is VBA, which would sit in a module in your workbook.
0

Featured Post

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month