Workarounds for Excel's addition problems
Now you know NOT to trust the precision of Excel’s results even for simple additions, what can you do about it?
As we’ve shown in recent articles (Excel SUM anomaly, More Excel addition strangeness and Excel addition problems – bug or not? among others) , Excel’s arithmetic isn’t as reliable as Microsoft would like us to believe. In this article we’ll look at what you can do about it and the limitations of the fixes.
The examples that Microsoft gives in a Knowledge Base article don’t give any proper idea about the scale of the problem – from their single example you’d have no idea that adding up a few amounts of money might not work out correctly.
The Microsoft ‘workarounds’ are summarised briefly with no clue about the limitations that the fixes have.
As usual, the Knowledge Base is about ‘damage minimisation’ more than presenting the full situation to Microsoft’s customers – which is where Office Watch comes in.
In very simple calculations the stray floating point errors might not affect you. Take this simple example:
-$1.23 |
$1.12 |
$0.11 |
$0.00000000000000012490009027033000000 |
The real answer is zero (ie $0.00000000000000000000000000000000000 ).
As Excel is currently released by Microsoft it has a problem with addition/subtraction especially when it involves a mix of negative and positive numbers as the result approaches zero.
Sadly you have to assume that any calculation, however simple, might contain floating point errors. Since results near zero are quite common (ie ‘balance sheet’ like situations) the Excel bug isn’t as rare a customer experience as Microsoft likes to make out.
There are two broad areas where the floating point error becomes a real problem for Excel users (we’d be interested in hearing about others):
- Comparison. Any time you compare one cell to another you need to allow for Excel’s inaccuracies. This doesn’t just apply to the common IF function but also conditional formatting.
- Scale. If you’re multiplying large numbers the ‘irrelevant’ error in Excel will start encroaching on your results.
Comparison
The comparison problem is the most common one that people will strike.
Anytime you compare two numbers in Excel you need to be aware that the number you see on the screen isn’t the entire number that Excel is comparing and that number might not be exactly correct. See What you see isn’t what Excel knows .
Most comparisons in Excel are with the IF statement (eg =IF(A9=0,”Yes”,”No”) but there are many others:
- IF
- LOOKUP
- HLOOKUP
- VLOOKUP
- COUNTIF
- COUNTIFS
- SUMIF
- SUMIFS
- AND
- OR
- NOT
This is used in cell formulas but also in conditional formatting.
Scale
The other problem is much rarer but if you’re dealing with really large numbers then it could be a problem.
A floating point error usually shows up far to the right of the decimal but if you’re multiplying by numbers in the tens of millions or more that error can start to show up in meaningful ways.
To use the above example again, in an admittedly unlikely scenario:
-$1.23 |
$1.12 |
$0.11 |
$0.00000000000000012490009027033000000 |
Now multiply that result by the economies of scale in a mythical factory run:
Production run | Cost per widget |
10,000,000 | $0.0000000012 |
100,000,000 | $0.0000000125 |
1,000,000,000 | $0.0000001249 |
10,000,000,000 | $0.0000012490 |
100,000,000,000 | $0.0000124900 |
The ‘obscure’ and ‘irrelevant’ error starts encroaching on the significant digits of a calculation.
Since floating point errors usually appear as you approach zero, scale problems aren’t often a problem. But it’s not a stretch to think of situations dealing with a high number of ‘widgets’ each with a cost or cost component in the tiny fractions of cents each. Multiply the small cost amounts by a large scale of production and floating point errors can intrude.
Of course, Excel isn’t just used by industry. Scientific and medical users of Excel routinely deal with ‘parts per million’ or smaller fractions and then multiply that out into large scales.
Workarounds
There are various techniques available to you, which one you use depends on your situation.
Sorting
You can reduce or even eliminate Excel’s arithmetic errors simply by re-ordering the list numerically before you do the calculations.
This should not work, the order of addition should not make any difference to the result of a SUM but Excel doesn’t do maths in the way we humans learnt at school.
If you have a list of cells and the order doesn’t otherwise matter, sort the cells into ascending or descending order. If you’re importing data sort the incoming data numerically by the field you’ll be SUM’ing.
You don’t always have this option. Often you want the list to remain in another order (alphabetical by name or grouped according to SKU). It’s quite possible you’re adding up several fields of different incoming data (cost price, wholesale price, retail price) so there isn’t a single order you can apply.
Using Precision as displayed
Microsoft’s favourite workaround for floating point errors is the ‘Precision as displayed’ option.
This option is somewhat buried at Excel Options | Advanced | ‘When calculating this workbook’ in Excel 2007:
or Tools | Options | Calculation in Excel 2003.
‘Precision as displayed’ will force Excel to calculate using only the digits displayed on the screen. Normally Excel displays one thing but calculates with the full precision number that’s stored in each cell – ‘Precision as displayed’ means Excel will only work with the displayed number.
It sounds like a great idea and if you’re dealing entirely in a single precision (eg 2 decimal places for money / accounting) then it’s often ideal. Many accountants and financial managers insist on using ‘Precision as Displayed’.
But beyond strictly money matters ‘Precision as displayed’ is a blunt instrument with traps of its own.
In standard Excel, changing the displayed decimals in a cell makes no difference to the results but ‘Precision as displayed’ can radically alter the results simply changing from, say, 2 decimals to 3 decimals. There’s a toolbar button to do just that – one click and the results start changing. This is especially a trap if the worksheet is shared with other people who might alter the cell display not realising the consequences.
‘Data will lose accuracy’ is the warning you get when you turn on ‘Precision as displayed’ and with good reason. When you make the switch, Excel strips out all the high accuracy values for cells and replaces them with the displayed numbers. It’s much safer to set ‘Precision as displayed’ for a new worksheet than convert an existing one. If you convert a worksheet, carefully check the displayed values of cells before switching over.
You also need to be careful that the fractions not displayed are truly irrelevant. For example worksheet dealing with money that’s adapted to a purpose that includes costs in the fractions of cents (ie half or quarter of a cent). Perhaps a stock price has dropped to a level where it’s quoted in half, quarter or eights of a cent – with ‘Precision as displayed’ to 2 decimals any results will be wrong.
Using ROUND
If ‘Precision as displayed’ doesn’t seem right for you then you have to carefully massage Excel’s numbers before you do any kind of comparison (see the list above).
The Round() function will round up or down a value to the precision you specify. It doesn’t change the value of the referenced cells.
The example we used earlier in this article is =IF(A9=0,”Yes”,”No”) but the more prudent formula is =IF(Round(A9,2)=0,”Yes”,”No”) . Now the value in A9 is forced to 2 decimal places before comparing with zero and any floating point errors to the far right of the decimal point are stripped away.
Round() will adjust a value up or down depending the threshold. If the significant digit is 1,2,3 or 4 it rounds down; 5,6,7,8 or 9 rounds up. For example:
Value | Round to 2 decimals | Direction of rounding |
1.233 | 1.23 | down |
1.234 | 1.23 | down |
1.235 | 1.24 | Up |
1.236 | 1.24 | Up |
That’s sufficient for most purposes but may not always be suitable. More recent versions of Excel have variations on Round():
- Roundup() will always raise the value to reach the rounding precision
- Rounddown() will always lower the value to reach the rounding precision
- Mround() rounds to the nearest multiple eg MROUND(100, 3) returns 99, the nearest value to 100 that’s divisible by 3 . You could use this in a country where small coins have been removed from circulation. In New Zealand MRound(n,0.10) or Australia Mround(n,0.05).
Other adjustment options
Other number adjusting options are available in Excel, they are usually not appropriate as workarounds for arithmetic errors but we mention them in case they suit your needs:
- Int() – returns on the integer part of a number – ie the left of the decimal point.
- Trunc() – is the reverse of Int() – it leaves the fractional part of the number – ie the right of the decimal point.
- Even() – rounds up to the next even integer
- Odd() – rounds up to the next odd integer
- Ceiling() – rounds up to the next number divided by the significant digit. It’s similar to Mround except that the rounding always increases the value.
- Floor()- rounds down to the next number divided by the significant digit. It’s similar to Mround except that the rounding always deceases the value.
The real solution
The real fix for this problem lies with Microsoft who could implement more accurate calculations in Excel if they chose to do so. We’ll look at the opportunities missed by Microsoft in a future article.
See Also
- How precise is Excel?
- Excel’s effect on economic policy
- Lookup from a list in Excel
- Excel addition problems – bug or not?
- Microsoft’s ‘Get out of jail free’ card for Excel
- Excel addition problems – Microsoft’s response
- More Excel addition strangeness
- Excel SUM anomaly
- What you see isn’t what Excel knows