You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
We sometimes get mails from our customers claiming to have found a calculation error in Excel, when in fact the calculation isn't wrong, but the side effects of binary floating point precision make it seem that way. Today's author: Jessica Liu, a Program Manager on the Excel team, discusses the way Excel performs calculations, explains why sometimes you see answers you may not expect, and provides some tips on how to avoid rounding issues.
Take a look at the following table:
I want to be able to quickly identify the cases where the absolute difference is greater than or equal to 0.005. So I apply a conditional formatting rule on the absolute difference column to format values greater than or equal to 0.005 to be red. As a scan down the table, I notice that the value of 0.005 is not highlighted. I check over my conditional formatting rule and the formula I used to calculate the absolute difference (=ABS(A2-B2)), they seem to be correct. I then increase the precision of the absolute difference column in order to get more precise results. I discover my results have changed. Why does 1.3240 - 1.3190 = 0.0049999999999999?
Have you ever encountered a similar situation where your spreadsheet does not give you the result you were expecting for a seemingly simple calculation? You have checked over your calculations and still cannot figure out where it went wrong. Well the scenario you are facing may be due to floating point precision.
Excel was designed in accordance to the IEEE Standard for Binary Floating-Point Arithmetic (IEEE 754). The standard defines how floating-point numbers are stored and calculated. The IEEE 754 standard is widely used because it allows-floating point numbers to be stored in a reasonable amount of space and calculations can occur relatively quickly.
The advantage of floating over fixed point representation is that it can support a wider range of values. For example, a fixed-point representation that has 5 decimal digits with the decimal point positioned after the third digit can represent the numbers 123.34, 12.23, 2.45, etc… whereas floating-point representation with 5 digit precision can represent 1.2345, 12345, 0.00012345, etc… Similarly, floating-point representation also allows calculations over a wide range of magnitudes while maintaining precision. For example,
Floating-point representation that has 4 digit precision:
1.1x10-1 x 1.1x10-1 = 1.21 x 10-2
Fixed-point representation that has 4 digit precision with the decimal point positioned after first digit:
0.110 x 0.110 = 0.012
All numbers expressed in floating-point format are rational numbers. Irrational numbers such as π or , or non-terminating rational numbers must be approximated. The number of digits of precision also limits the accuracy of the numbers. Excel store 15 significant digits of precision. For example, the number 1234567890123456 cannot be exactly represented if 15 digits of precision are used.
The IEEE 754 floating-point standard requires that numbers be stored in binary format. This means a conversion must occur before the numbers can be used in calculations. If the number can be represented exactly in floating-point format, then the conversion is exact. If not, then the conversion will result in a rounded value which will represent the original value. Numbers that appear exact in the decimal format may need to be approximated when converted to binary floating-point. For example, the fraction 1/10 can be represented in the decimal format as the rational number 0.1. However, 0.1 cannot be represented precisely in binary floating-point of finite precision. 0.1 becomes the repeating binary decimal 0.0001100110011…, where the sequence 1100 repeats infinitely. This number cannot be represented in a finite amount of space. So in Excel, it is rounded down by approximately 2.8E-17 when it is stored.
A floating-point number is stored in binary in three parts within a 65-bit range: the sign, the exponent, and the mantissa.
The sign stores the sign of the number (positive or negative). 0 represents a positive number while 1 represents a negative number.
The exponent stores the power of 2 to which the number is raised or lowered. The exponent field needs to be able to represent both positive and negative exponents. To avoid having to store negative exponents, a bias value is added to the actual exponent. The bias for double-precision numbers is 1023. For example, a stored value of 1000 indicates an exponent of 1000 - 1023, or -23.
The mantissa stores the actual number. It is composed of an implied leading bit and the fraction bits. The storage size of the mantissa determines how close two adjacent floating point numbers can be. The mantissa and the exponent are stored in separate components. The precision of a number varies depending on the size of the mantissa. Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308; however, it can only do so within 15 digits of precision.
Example 1: Loss of Precision When Using Very Large Numbers
The resulting value in A3 is 1.2E+100, the same value as A1. This is because Excel stores 15 digits of precision. At least 100 digits of precision would be required to calculate the formula above.
Example 2: Loss of Precision When Using Very Small Numbers
The resulting value in cell A1 is 1.00012345678901 instead of 1.000123456789012345. This is once again is because Excel stores 15 digits of precision. At least 19 digits of precision would be required to calculate the formula above.
Example 3: Repeating Binary Numbers
Many combinations of arithmetic operations on floating-point numbers may produce results that appear to be incorrect by very small amounts. For example, the equation
may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0. This is due to the fact that the IEEE 754 standard requires numbers to be stored in binary format. As I described earlier, not all decimal numbers can be converted exactly to binary, as in the case of 0.1. The conversion caused the loss of precision.
Let us go back to my very first example where my conditional formatting seemingly did not work. I know now that was due to the fact that the numbers I was using to calculate the absolute difference did not have exact binary equivalents. This resulted in 1.3240 - 1.3190 = 0.0049999999999999.
There are two basic ways in which you can compensate for some of the errors due to floating point calculation. The first method is to use the ROUND() function. The ROUND() function can be used to round the numbers to the number of decimal places that is required in your calculations. For my absolute difference column, I only require 4 decimals of precision. So I change the formula in the absolute difference column from:
My conditional formatting rule works as expected now since 0.0049999999999999 has been rounded to 0.0050.
The second method to prevent rounding errors from affecting your work is by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps:
Going back to my absolute difference example, I set the number format to show four decimal places, and then I turn on Precision as displayed option. Since the display value is the actual value in the cell now, my conditional formatting works properly!
It is important to note that once the workbook is saved, all accuracy beyond four decimal places will be lost. This option affects the active workbook including all worksheets. You cannot undo this option and recover the lost data so save your workbook prior to enabling this option. This option is generally not recommended unless you are sure more precision will not ever be needed for your situation.
I am fairly confident that
1.1x10-1 x 1.1x10-1 = 1.21 x 10-2
not 1.44 x 10-2 as stated above.
"The second method to prevent rounding errors from affecting your work is by using the Precision as displayed option."
This is a scorched-earth approach which should be avoided at all costs. Your gentle caveat:
"It is important to note that once the workbook is saved, all accuracy beyond four decimal places will be lost. This option affects the active workbook including all worksheets. You cannot undo this option and recover the lost data."
should be displayed in large bold red capital letters, and the follow-up sentence changed to
"It is recommended that you never invoke this option."
Rick: thanks for pointing out the typo. It has been corrected.
Example 1: The resulting value in A3 should be 1.2e+200 (not 1.2E+100), same as A1.
Thanks for the great post with good examples. I have to agree with the other commenter that the second option given seems more dangerous and troublesome than is worth in many (most?) cases, and seems deserving of an even bolder warning.
This site provides free Microsoft Excel Training with video examples.
Jon / step: I understand your concern regarding the Precision as Displayed option. Since there are cases where using Precision as Displayed is a valid alternative, I don't think I should recommend that it *never* be used, but I do agree that a stronger warning should be made so I've updated the blog post to reflect that.
Thanks for your input.
My Excel add-in, xlPrecision, has been resolving these problems for Excel users since 2003. See precisioncalc.com/xlprecision.html and precisioncalc.com/Testimonials.html. BTW I am a former MS Excel product support escalation lead at Microsoft.
Completely off topic but I'm "desperate" for an answer!
Create a table in Excel 2007 via Insert>Table
Suppose the table is named Sales.
Column 1 = Quantity
Columns 2:13 = the months; Jan, Feb, Mar....Dec
I want to multiply Quantity by each individual month so I use this formula:
If I copy that across I get:
So, how do I make Quantity an absolute reference?
This works but is totally undesireable since it is now volatile:
Seems to me the standard use of the $ sign would be the "intuitive" means of
making the column absolute but no such luck!
I've searched high and low for this and have come up with zip!
> Why does 1.320 - 1.3190 = 0.0049999999999999?
Yes that's really a good question. I suppose this is another typo. It should be 1.324 instead of 1.320, I suppose (that's what you can see in the spreadsheet).
Bjoern: thanks for catching that typo. I've replaced 1.320 with 1.3240.
Biff: Structured References have many characteristics of absolute references, but you have illustrated one case where it does not. Unfortunately there is no way to prevent that reference from adjusting when you fill the formula. You have already discovered one workaround. The other alternative is to use a standard A1 reference.
Thanks for your feedback.
"Have you ever encountered a similar situation where your spreadsheet does not give you the result you were expecting for a seemingly simple calculation? You have checked over your calculations and still cannot figure out where it went wrong. Well the scenario you are facing may be due to floating point precision."
This would be a great way to start an episode on Discovery Channel... or perhaps the shopping channel for math ppl