Abstract
“Always listen to experts. They’ll tell you what can’t be done and why. Then do it.” [Robert Heinlein]
Criticism should be constructive - in general I try to show how things could or should positively be done. But during more than 30 years of Excel practice I encountered some examples which could and maybe should be avoided.
A table of what you should avoid
Please look at this list as an intellectual challenge and take it with a grain of salt.
| # | Do not use this | Why? |
|---|---|---|
| 1 | Excel Add-in freeware like Morefunc | You should have a license or at least the free source code for add-ins so that you can help yourself in case the provider vanishes. If not, it is a security threat, too. For Morefunc the provider has obviously disappeared, no source code seems to be available, some of its functions no longer work with Excel 2007 or later. I think it’s irresponsible to suggest to use it anymore |
| 2 | Worksheet formulae you do not understand | How would you know that it is correct? After row or column insertions/deletions? How would you adapt it to future needs or even to simple sheet rearrangements? With a UDF you only need to understand the functionality and the interface. This is called encapsulation |
| 3 | Enable option “Precision as Displayed” from Calculate tab on the Options dialog (Tools menu, up to Excel 2003) or from Excel Options (Advanced menu, Excel 2007 onwards) | A display format change in any input cell or intermediary cell can ruin the results. Permanently |
| 4 | Enable option “Iteration” from Calculate tab on the Options dialog (Tools menu, up to Excel 2003) or from Excel Options (Formulas menu, Excel 2007 onwards) | You will not see accidental circular references |
| 5 | 1904 date system (to be able to present negative time = hours/minutes, for example) | Almost everybody is using the 1900 date system. If you switch between these systems you will need to remember the number 1462 by heart (to add or subtract it after CTRL + c with ALT + e, s, v and then d [add] or s [subtract]) |
| 6 | Do not test double precision numbers with = for equality. Use a test like ABS(a - b) < 1E-13 instead | =(43.1-43.2)+1=0.9 does not result in True as you might think, for example. See Microsoft’s article on this topic (external link!) here. Best read on this topic is Goldberg’s article (external link!) “What every computer scientist should know about floating-point arithmetic” |
| 7 | Count or sum specified cells conditionally for all different elements of a list with SUMPRODUCT | SUMPRODUCT is powerful but for a statistic like sum or count on all elements of a list there are better ways like a pivot table or PowerQuery |
| 8 | Worksheet function INDIRECT | This function is volatile which means it gets recalculated each time you press F9. If you use INDIRECT something is terribly wrong with your worksheet design |
| 9 | Worksheet function AGGREGATE | If you exclude hidden rows or exclude error values, no auditor will accept this (I bet) |