One of the more frustrating aspects of working in Excel is how the application sometimes appears to add values together incorrectly, providing results that appear to be incorrect by very small amounts. In this tip, we will explore the source of these types of errors and seek solutions to this issue.
Consider the set of data in cells A1 through A3 of Figure 1. A casual inspection of the data – with perhaps the help of a calculator – reveals that the expected sum of the data is zero.
Figure 1 - Basic Data Set For Adding In Excel
However, upon entering the formula =SUM(A1:A3) into cell A4, we find that Excel believes the sum of the three cells is a small fractional value. Further, we realize the same result when using =SUBTOTAL(9,A1:A3) in cell A5 and =A1+A2+A3 in cell A6, as shown in Figure 2.
Figure 2 - Data With Erroneous Totals
If any of the erroneous values in cells A4 through A6 are used in a test to determine if they are equal to zero – such as applying conditional formatting – result of the test will be incorrect because the summed values are not in fact equal to zero.
According to Microsoft, the source of these types of errors is the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requiring that numbers be stored in a binary format. In binary format, some numbers simply cannot be stored with perfect accuracy. As such, even though a number appears to be one value, it might actually be rounded when it is stored. (For more information on the source of the problem, visit http://support.microsoft.com/kb/214118.)
The two most commonly-used methods of solving the problem are to 1) add a ROUND function to a summing formula and 2) use Excel’s Precision as Displayed option. Following the example shown in Figure 1, entering the formula =ROUND(SUM(A1:A3),2) into cell A4 produces the anticipated result of zero as shown in Figure 3.
Figure 3 - Adding A ROUND Function To A SUM Formula
Alternatively, you can use Excel’s activate Excel’s Precision As Displayed option to force each value in the workbook to dynamically round to the number of decimal places in the cell format. This has the effect of truncating all of the fractional values beyond the decimal place format. To activate Precision As Displayed in Excel 2007, click the Office Button, Excel Options, and Advanced. Scroll down the list until you reach the option labeled Set precision as displayed and check the corresponding box. In Excel 2010, activate the option by clicking the File tab, Excel Options, and Advanced. Then scroll down the list and check the box next to Set precision as displayed. When enabling this option, you will receive a warning message in Excel indicating the “Data Will Permanently Lose Accuracy.” The nature of this message is that Excel will permanently round and truncate the data to the current number of decimal places in the cell format, so be sure to make a backup of the workbook before you enable the option if you are unsure about the consequences of enabling Precision As Displayed.
While you cannot change how Excel handles floating point calculations, you can control how the results of these types of calculations appear in your spreadsheets. By using ROUND and Precision As Displayed, you can be sure that your results are correct.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips107.