|
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.
Article posted: Monday, 02 June 2008
[1] 2 Next »
[
View on Single Page ]
More from Office Watch:
|
RSS feed for this category