nishi173
05-19-2011, 06:16 PM
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.
Overview 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,Microsoft Office 2007 Sale (http://www.key-office-2010.eu/office-2007-key), 12.23,Windows 7 Starter Key (http://www.key-office-2010.us/windows-7-key), 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,Office Home And Business 2010 Key (http://www.key-office-2007.ca/office-2010-key), 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.
Structure of a Floating Point Number 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 Bit
The sign stores the sign of the number (positive or negative). 0 represents a positive number while 1 represents a negative number.
The Exponent
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
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.
Common Examples of Error Due to Floating Point Calculation 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
=1*(.5-.4-.1)
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.
Correcting Precision Errors 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:
=ABS(A2-B2)
To:
=ROUND(ABS(A2-B2),4)
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:
Click Microsoft Office Button -> Excel Options -> Advanced In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.
Click OK. Going back to my absolute difference example,Microsoft Office 2010 Serial (http://www.key-office-2007.co.uk/office-2010-key), 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,Cheap Office 2010 (http://www.key-office-2007.co.uk/office-2010-key)!
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.
<div
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.
Overview 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,Microsoft Office 2007 Sale (http://www.key-office-2010.eu/office-2007-key), 12.23,Windows 7 Starter Key (http://www.key-office-2010.us/windows-7-key), 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,Office Home And Business 2010 Key (http://www.key-office-2007.ca/office-2010-key), 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.
Structure of a Floating Point Number 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 Bit
The sign stores the sign of the number (positive or negative). 0 represents a positive number while 1 represents a negative number.
The Exponent
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
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.
Common Examples of Error Due to Floating Point Calculation 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
=1*(.5-.4-.1)
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.
Correcting Precision Errors 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:
=ABS(A2-B2)
To:
=ROUND(ABS(A2-B2),4)
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:
Click Microsoft Office Button -> Excel Options -> Advanced In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.
Click OK. Going back to my absolute difference example,Microsoft Office 2010 Serial (http://www.key-office-2007.co.uk/office-2010-key), 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,Cheap Office 2010 (http://www.key-office-2007.co.uk/office-2010-key)!
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.
<div