CPearson.com Excel Newsletter 08-August-2007

Author: Chip Pearson, email: chip@cpearson.com
Release Date: 2007-August-27
Website: www.cpearson.com
Document Format: XHTML 1.1
Issue: 6

You are receiving this email because you subscribed to the CPearson.com Excel Newsletter. If you are receiving this message in error or you wish to change or cancel your subscription, please go to the CPearson Newsletter Management Page or email Chip Pearson at chip@cpearson.com.

Back issues of the CPearson Excel Newsletter are available in the Archives.

In this issue, we will look at circumstances in which Excel may seem to return the wrong result for a calculation. In nearly all cases, this apparent error is no error at all, but rather the result of one of to two things: the difference between the displayed value of a cell and the actual value of a cell, or a limitation to the accuracy in which numbers are stored in a computer. This second limitation isn't unique to Excel or to Microsoft software. It is a limitation well known to programmers of any software product on any system.

Excel always stores and calculates numbers to fullest level of precision possible, regardless of how the input and output cells may be formatted for display. That means that Excel uses 15 digits, the total number of digits to the left and the right of the decimal place, even if you are displaying only two decimal places in the input or output cells. For example, in cells A1:A8, enter the formula =1/8. Then, in cell A9 enter the formula =SUM(A1:A8). Format cells A1:A9 to display 3 decimal places. Cells A1:A8 will display 0.125 and cell A9 will display 1.000. This is correct and what we expect. Now, format cells A1:A9 for two decimal places. The cells A1:A8 will now display the value 0.13 and cell A9 will display 1.00. But 0.13 times 8 is 1.04, not 1.00. Is Excel wrong? Is this a bug?

No, Excel is not wrong. There is no bug. When Excel calculates the formula =SUM(A1:A8), it uses the actual values in cells A1:A8, not the displayed values in A1:A8. Regardless of how the cells are formatted for display, Excel uses the underlying values, will all 15 digits. In this case, Excel uses the values 0.125, not 0.13, in the calculations.

It is possible, but not recommended, to force Excel to use the displayed values, not the actual values, in calculations. This setting is controlled by the Precision as displayed option on the Calculate tab of the Options dialog on the Tools menu. To see why this is probably not desirable, enter the following: In A1:A8, B1:B8 and C1:C8, enter =1/8. Then in cells A9, B9 and C9 enter =SUM(A1:A8), =SUM(B1:B8) and =SUM(C1:C8). Format column A for three decimal places. Format column B for two decimal places, and format column C for one decimal place. Now enable the Precision as displayed setting. You'll see that cell A9 displays 1.000 and cell B9 displays 1.04 and cell C9 displays 0.8. Therefore, we have three different values, all of which are summing essentially the same values, the values 1/8. Which one of these three values is correct? They're all correct. This can lead to confusing and incorrect caclulations.

If you need to take rounding into account, you can use the ROUND function in an array formula. (See www.cpearson.com/Excel/ArrayFormulas.aspx for more information about array formulas.) The ROUND function rounds numbers to a specified number of decimal places. For example, the formula =SUM(ROUND(A1:A8,2)) entered as an array formula (press the keys CTRL SHIFT ENTER rather than just ENTER after you type the formula), will round the values in A1:A8 to two decimal places and then SUM those rounded numbers. Therefore, if A1:A8 contains =1/8, Excel will round those numbers from 0.125 to 0.13 and then SUM to give a result of 1.04 rather than 1.00. Note that we rounded the individual values before passing them to the SUM function. This means that the array formula =SUM(ROUND(A1:A8,2)) is quite different from the formula =ROUND(SUM(A1:A8),2). The first formula rounds the values in A1:A8 before summing, while the second formula sums the values and then rounds the result. Other functions that you can use for rounding include:

Another cause of apparent errors is due to the inherent limitations of storing numbers in computer. Excel, like nearly all other software programs, use what is called the Double Precision Floating Point data format. This format, which an industry standard, not limited to Excel or to Microsoft products, allows for 15 digits of precision. The term precision refers to the number of digits on both the left and right of the decimal point that can be accurately represented. If the number of digits on both the left and right of the decimal point exceeds 15, Excel will round off at 15 digits. For example, in cell A1 enter the number 123,456,789,012,345. Then in cell A2 enter =A1+1. Since A1 is 15 digts, A2 accurately displays 123,456,789,012,346. Now change A1 to 1,234,567,890,123,456. This number is 16 digits, exceeding Excel's limitation of 15 digits. Therefore, Excel rounds it to 1,234,567,890,123,450. The last digit, 0 is the result of rounding. Cell A2 is also rounded, so A1+1 is rounded to 1,234,567,890,123,450. This seems wrong, since it is the same number as A1, and mathematically, A1+1 certainly doesn't equal A1. And mathematically speaking, it is wrong. But it is not a "bug" by any means. This rounding behavior is an inherent limitation of computers and needs to be taken into account when working with large numbers or number with many decimal places.

This limitation also determines how many places to the right of the decimal place that can be used. For example, in B1, enter 123.456789012345. This has a total of 15 digits, three to the left of the decimal and twelve to the right. In cell B2 enter =B1+0.000000000001. That is eleven 0s to the right of the decimal place followed by a one. This remains within the limits of 15 digits, so the result is accurately 123.456789012346. Now, in B3, enter =B1+0.0000000000001. This is twelve 0s to the right of the decimal point followed by a 1. Mathematically, this should be 123.4567890123451. However, this exceeds Excel's limitatin of 15 digits (it is 16, three to the left of the decimal point plus 13 to the right), so Excel rounds the result at twelve places to the right of the decimal point and rounds everything that follows.

For a much more in depth discussion of the Double Floating Point standard, see www.cpearson.com/Excel/Rounding.htm.

If you need to store numbers longer than 15 digits, but you do not need to perform calculations on those numbers (such as telephone numbers or credit card numbers), you can tell Excel not to treat those as real numbers but rather as text. When do this, Excel will display the numbers with as many digits as you enter. However, rounding will occur if you attempt to use those numbers in caclulations. To enter numbers as text, you can format the cells Text format before entering the number, or you can preceed the number with an apostrophe ('). The apostrophe will not display in the cell, but will appear in the formula bar.

It is worth noting that even though Excel is limited to 15 digits of precision, it can work with numbers as large as about 10^308 (308 digits to the left of the decimal place) or as small as 10^-308 (308 digits to the right of the decimal point). However everything past the 15 digits of precision is rounded. For example, Excel can display the number 10^25 but (10^25)+1 can't be calculated accurately since it exceeds 15 digits of precison. It will be rounded at 10^15.