.Perhaps a strange title for this article at first sight. Does Excel make calculation errors? Yes, Excel makes calculation errors and they are usually not even classified as bugs. VAccording to the specification, Excel calculates with an accuracy of 15 numbers. These are the number of numbers before and after the comma added together. So it is to be expected that when working with numbers close to this number, rounding differences will arise.. That is not surprising, but it is strange that in some cases weird rounding differences arise much earlier. In this article, a number of examples of these "calculation errors" are discussed, including an explanation of cause, effect and what can be done about it.
Examples
Here are some examples of these rounding problems. These examples have been tested in a number of Excel versions, but are likely to be found in (almost) all Excel versions.
In the Excel sheet below the number -1.23 has been entered in A1 and the number 1.12 in A2. Then these numbers are added. The result is -0.11. If 0.11 is added there again, the result is not exactly equal to 0, as shown in A5.
Another similar example. Enter the numbers 28.552, 27.399, and 26.246 in A1 through A3, respectively. And put in column B the formulas as indicated in column C.. Also in this case the difference is not exactly equal to 0.
Or enter the number 7745.11319561 in any cell. It can be seen that Excel then automatically makes it 7745.113195609990. To be able to see this, the number of displayed decimal places must be adjusted. Another example is to enter the number 0.1 in A1. Then in A2 put the formula =A1+A$1 and copy this formula down at least 60 rows. Now look in cell A60 and set the number of decimal places to be displayed to at least 14. Normally, the result of the calculation should be 6, but it is actually 5.99999999999999. Just a few last examples that do not produce exactly the right result:
=7.11-7.1
=7.11-7.01
=1*(0.5-0.4-0.1)
=43.1-43.2
Cause
These calculation errors are caused by rounding, truncation of decimals and the storage method of numbers. This is not a specific problem for Excel, but it is true for many if not all spreadsheet programs. The method of storing numbers in particular can sometimes lead to strange unexpected rounding differences. The cause of these calculation errors has to do with the way in which Excel stores numbers internally in binary.Basically, numbers after the decimal point are stored in binary as an addition of fractions (12)n, where n is the position number. So the first decimal place stands for the number 12, the second position for 14, the third for 18, etc.. In binary notation, a 0 in a position means that the relevant number is not present and a 1 means that this number is present. So the number 0.5 is displayed in binary as 0.1, the number 0.25 as 0.01 and the number 0.75 as 0.11 (=0.5+0.25).
For example, just as in the decimal system a fraction like 16 cannot be represented exactly, because the sixes in the result 0.1666 are repeated indefinitely, there are also decimal numbers that cannot be represented exactly in binary form, such as 110. Decimal this number can be represented exactly with 0.1 but binary it is represented as 0.0001100110011… This is a series that is not finite. So the bottom line is that the decimal number 0.1 cannot be exactly represented by adding fractions (12)n. This creates small differences in numbers far behind the decimal point. Normally it is difficult to predict when this problem will occur.
Consequence
But what are the consequences of these errors? Unless numbers are used close to the precision of Excel (15 characters), the consequences will normally not be that great as the differences are very small. But with compare and search functions such as VLOOKUP, IF and MATCH, this problem can have serious consequences. Then these functions will give an error or a wrong result because the search value cannot be found. For example, it searches for the number 78 and a number 78 exists and still returns an error that the value cannot be found. This is because there is no exact match because due to the sketched problems the number 78 turns out to be not 78 but for example 78.0000000000045.
Solution
The solution to these errors is actually quite simple. It's just a matter of rounding the number in question with the ROUND function (number, number of decimal places). Please note, this concerns the ROUND function and not the number of decimal places that can be set in the cell properties. The cell properties do not change the underlying number, only the format of the number. This is therefore an optical change, while the ROUND function does change the number.
For the sake of completeness, here is another rigorous method sometimes mentioned to fix this. This can be done by checking the 'Precision as shown' in menu option File → Options → Advanced (there are Excel versions where this choice can be found elsewhere). This will round all numbers as shown on the worksheet. Note: this cannot be undone. The advice is to be careful with this, because on the one hand this may have eliminated the problem with binary calculation errors, but on the other hand, calculation errors arise again because all numbers in the worksheet lose their accuracy. Advice: only use this option if the consequences are known and manageable.
Questions / suggestions
Hopefully this article helped you to deal with calculation errors in Excel. If you have any questions about this topic or suggestions for improvement, please post a comment below.
