What happens when a good Excel worksheet goes bad.
Based on an article by Helen Bradley
What happens when there’s an error in a worksheet? The answer is that it depends on what the worksheet is calculating and how bad the error is. Theoretically if an error were to over calculate doses of a highly potent drug then an error could kill, if it were to under calculate a company’s cash flow situation, it could harm the financial stability of the company.
Apart from some scandalous horror stories about what spreadsheet errors have cost some organizations, there’s an underlying message here that spreadsheet errors exist and they cost money. If you’re creating spreadsheets for financial or other important purposes it will pay you to take the approach that your worksheet could (and probably does) contain errors and to do what you can to track and fix them.
WHAT ERROR IS THAT?
There are three basic types of errors to look out for; Syntax errors, runtime errors and logic errors. Here’s a quick look at the differences between them. Syntax errors result from you misapplying Excel’s own language. For example, a syntax error occurs when you use an =MAXIMUM function (instead of =MAX) or you type =MAX() omitting a compulsory argument (the range of cells to apply the function to). Syntax errors are generally easy to track because Excel finds them for you. Try typing -=MAXIMUM(A1:A3) and the cell will contain #NAME? indicating a problem with the formula.
A Runtime error occurs when you actually run a program. In Excel terms you’d encounter a runtime error if, for example, a cell containing data which you’ve used as the divisor in a formula were altered to zero. Try this, type the number 10 in cell A1 and then the formula =200/A1 in cell A2. It all looks fine, until you change the value in cell A1 to zero and you get a runtime error. Runtime errors often occur intermittently and, because it can be hard to predict the exact set of circumstances which will trigger the error, they are often difficult to find and fix until they actually occur.
Logic errors are even harder to find because they don’t give off visual clues to the fact that they exist. The worksheet looks ok, but the answers are wrong because of some faulty logic in the design, caused generally by the person who wrote the formulae. For example, a simple logic error could be created by the formula =2+3*4. Some people intend this to be evaluated as 2 + 3 = 5 and then 5 * 4 = 20. That’s not how Excel will evaluate it. If you don’t believe me, try it. Excel uses a specific order of precedence to evaluate this (and every other formula) and it performs the multiplication first and then the addition — the answer is 14. It’s a ridiculously simple (and understandable) mistake to make and it’s very, very hard to track. It’s a logic error! (To learn more about Order of Precedence, type ‘order of precedence’ into Excel’s Answer Wizard).
FINDING ERRORS USING SIMPLE MATH
A simple way to locate errors is to use some math tools you probably learned in High School — acchh! remember High School… gives me palpitations just to think about it. However one math teacher I had used to get us to perform rough calculations before we could do the accurate ones. So we’d make a quick guess at the result we were going to get before we set about calculating it.
There’s no hard and fast rule about how to make rough calculations, except they should be performed quickly and give you a ball park figure for your answer.
Another way to check complex formulae is to use simple figures rather than complex ones and check the results are what you’d expect (simple figures make manual checking easier too). If the simple figures work, there’s a good chance more complex ones will too.
You can also check formulae by using known results, i.e. data you know the correct answer for. For example, if I were to use the PMT function to calculate the repayments on a loan, I’d punch in the data from the Excel help topic on PMT functions (it’s there, it’s free and I don’t have to go hunting for it) and see if I got the results the help file suggests I should. If I don’t, either the data in the Microsoft’s help file is wrong or I made a mistake — either way I have some more work or research to do before I can trust the formula that I wrote.
In a work situation you may already have some hand calculations you’ve made or ones that have come from another source. Punch those numbers into your formula to check it and don’t do anything else until you get the same result, or until you understand why you’re not getting it.
Auditing Toolbar
Finding your way around a complex worksheet is easier if you put the Formula Auditing toolbar on (right-click on the toolbar and choose Formula Auditing).
This toolbar has many tricks:
Error checking
This will scan the worksheet for problems. It won’t pick up maths errors or incorrect cell references.
Tracing
The ‘flowchart’ looking buttons let you see which cells are required to calculate a particular cell (precedents) or see which cells are affected by a change in the current cell (dependents).
This can help you work out the logic (or lack of) in a worksheet.
Watch Window
Sometimes it can be hard to keep an eye on a cell because it isn’t in view. The Watch Window lets you nominate cells to watch the changing values as you tinker with the worksheet.
GOOD DESIGN IS A KEY ERROR PREVENTION TOOL
Another beneficial tool for keeping errors to a minimum is to design your worksheets so that the constant data you use is stored in a highly visible place. For example, if you are making calculations using county tax rates, put the figure (7.5% in my sunny Californian home town) into a cell and, next to it, type County Tax Rate or something equally descriptive.
Then, whenever you need to include the tax rate in a formula include a reference in your formula to where the 7.5% is stored, instead of typing the value 7.5% in the formula itself. So, if the tax rate were stored in cell A2, a formula for calculating tax on an item may read =B6*A2 instead of =B6*7.5%.
There are a few reasons for taking this approach. One is that it’s dead easy to check you’re using the current tax rate, you only have to check one cell. If the tax rate goes up (or down), ditto, change one cell and all the calculations which use the tax rate will change automatically too.
You can, of course, name the cell if you like and instead of referencing the cell location you can then use the name in your formula.
DOCUMENTATION – DON’T PUT OFF UNTIL TOMORROW WHAT YOU SHOULD DO TODAY!
Often problems in worksheets don’t occur when they are first created but occur later on when they’re handed off to someone else to maintain, often because the designer has moved up or out of the organization. The person inheriting the worksheet doesn’t fully understand the assumptions underlying it and how it has been designed so, when something needs updating, they don’t get the changes right or they don’t change everything that needs changing, or they simply don’t know that changes are even required. You’ll know yourself it is often hard to remember your own logic when you’re working with your own designs. How much worse will it be when you’re trying to reverse engineer someone else’s design? Many of these problems could be overcome by producing proper documentation for the worksheet.
Allocate a worksheet or more in each workbook to document your work. Include basics like your name and the date you created the workbook. Outline what its purpose is and how it makes its calculations. Also list any assumptions you’ve made and anything you can think of now, as you’re creating it, that you would want to know in the future if you were charged with the task of updating it.
Documentation is a time for honesty, not whitewashing. If you know there are problems with the worksheet and it fails under certain circumstances or it has only limited use, note this in your documentation. Detail the formulae that you’ve used and the data that is used to make the calculations.
You can also add a ‘To Do’ list of things you’d like to add to the worksheet in future. Not only is it a good reference for you, it can help other people work out what the worksheet can and cannot do.
At worst you’ll save yourself hours of work if you need to make changes in the future and, at best, your thoroughness may save someone else from a costly mistake. Remember that the time to document a workbook is while you are working on it – not tomorrow!
