Greg asks about error detection in spreadsheets; a great topic, I think. We’ve all read the studies that say 95% of all spreadsheets have errors. I tend to think those numbers are exaggerated, but I may be wrong. Speaking for my self, I think my error rate is well below 95% (like 85%).
There are probably some great error-catching techniques out there. Sadly, my number one error catching technique is happenstance, that is, I just happen to see an error. I think that’s true of most people.
I’m trying to think of how I implement error checking into my spreadsheets. When I crossfoot, I build error checking into the formula. One technique that I use quite a bit is the formula that returns nothing if there’s no error. For instance, in my balance sheet assets must equal liabilities and equity. I have a formula that looks like
The font is red, and huge. There’s no mistaking when my balance sheet doesn’t balance because it practically hits me in the face. The 10 months of the year when there’s no problem, the formula is unobtrusive.
I have an estimate spreadsheet where bids are entered and summed. One of the error checking techniques I use there is a Change event. I use a SelectionChange event to store the value of the currently selected cell. If that cell changes by more than 10%, a warning is displayed. Ten percent is a pretty big difference and it usually indicates a keying error, although not always.
The warning for the above technique is that a comment is added to the cell indicating the old and new values. Generally, I don’t like error checking that stops the flow of entering data. Sometimes you have to stop everything because a piece of data must be right. However, it’s usually preferable to let the user keep entering data, then go back and fix what needs fixing.
I liken it to me typing a letter in word. I type three words per minute. I can type hundreds of characters per minute, but they stop counting words after my second mistake. That’s just stupid, in my opinion. I type my letters as fast as my fingers will go and I don’t give a hoot about mistakes. When I’m done, I go back and fix the mistakes and apply any formatting. That’s the way I want my data entry too. Let me put the data in, then go back and fix the errors. That’s not practical in every case, but in most cases it’s preferable to me.
What kind of error checking techniques do you build into your spreadsheets?
[quote]
The font is red, and huge.
Is this because are using conditional formatting as well or the “font is red and huge” all the time?
doco
Being an accountant I have tons of error checking techniques. My favorite story involves the Sarbanes-Oxley audits. The SOX auditor asked how do you know that your reports are recociled to each other. We told him that we had a smiley face. Since then the smiley face has been the norm for our company. If all reconsiles we have smiley faces. I have a large application that involves three areas. If all three areas agree then I reconcile. It is imposible to look at the numbers all day and compare but to look at a smiley face or sad face I know instantly that I am or am not reconciled. Simply use an IF/Than statement. If reconciles then capital J, if not capital L. Change your font to Wingdings for that cell.
Most errors, I have found, are casued by poor spreadsheet design. My bigest beef is when someone likes to put an empty column between two other columns. Then they hide the emply column. Then they try to sort. One side gets sorted and the other remains the same. This has casued some huge errors.
On the plus or minus 10% thing, Are those two event handlers, one tied to Worksheet_Change, storing values to some range for later comparison by the SelectionChange event? Or is it a single event handler calling another? Code would be interesting to see, if you are willing to part ith it…
I’ve been doing some informal questioning among my professors, classmates, and business contacts on this topic and have come up with basically nothing. If any review is done at all, it comes down to one of these:
1. Have someone else familiar with the subject matter look it over to see if the conclusion is in line with expectations
2. Use the auditing toolbar or a custom built auditing tool (such as the John Mansey’s trace tool)
3. Tick and tie key numbers back to the source document
4. Compare it to a prior spreadsheet if available to see if the conclusion is consistent
For quite some time now, I’ve been kicking around the idea of offering a spreadsheet auditing service, but haven’t really thought much beyond the basics of what needs to be done. In general, I think any business (in the finance department, at least) can pinpoint a few key spreadsheets that they rely upon in decision making where it would be valuable to have some kind of assurance service that the formulas do what they purport to do and that there are no basic mistakes throughout. I can imagine that could get quite detailed or be very basic depending on the type of business and the complexity of the spreadsheet.
I’ve looked at various Excel consultant websites and it doesn’t seem like this is something really talked about except in passing. The point is to go beyond the basic streamlining and redevelopment of templates that seems to be the basic business model for these consultants.
I don’t mean to hijack the thread here, but I am curious as to what some of you professional users think about this idea.
A site I still refer to, even though it’s been out of business for a while, is John Raffensperger’s SpreadsheetStyle:
http://www.mang.canterbury.ac.nz/people/jfraffen/sscom/index.htm
It’s got a section on auditing. The info is great, and most of the links are first rate.
For a short treatise on how spreadsheets *should* be written, I don’t think you can beat John’s “The New Guidelines for Writing Spreadsheets”:
http://www.mang.canterbury.ac.nz/people/jfraffen/spreadsheets/index.html
Hi all
I like Dick use a spreadsheet for estimating. Most of our projects (being a sub) are in the 15 to 20 million range. I use every thing possible for error checking
I set up the template then I three ways up front to check the grand total from the sub sheets to the mains sheets. I also use the cross check. Then I send it out for auditing by another employee manualy. I use Arron Bloods explode add in for checking formulas you must really try it.
As for as being a professional auditor for spread sheets I would not want the libilty for this. Any spread sheet is an error waiting to happen.
Thanks Dick for a good disscusion.
Jim
#2 I use smiley faces for macro buttons I like to click and frown faces macro buttons I do not like to click
Smiley and frown faces are used for a lot of things lol :) :(
No Smileys Only Frowns when working
JE: That’s a great link… Kiwi too :)
Bill: What are examples of macro buttons you don’t like to click?
The most common problem I’ve run into is someone using a spreadsheet like a typewriter, and changing the formula I’ve inserted into a number that they think is correct for some reason.
I’ve designed budget sheets for others to use, and I use a technique like Dick’s crossfoot example as one initial check. But since some of the users tend to see the word “Error” and find interesting ways of solving for that error, I take it a step further to protect them from themselves. (Their “solutions” have included removing the word “Error” from the formula, replacing the “offending” formula with one that totals in only one direction, and so on.)
To prevent this, I use a similar formula which, if false, returns “ERROR IN THIS SHEET–TOTALS ACROSS DO NOT MATCH TOTALS DOWN”. I put this in a remote location on the sheet, then put a text box right across the top of the sheet (where things like the client or market name go usually) with a link to the error-checking formula. Big letters, etc. If they “fix” my checking formula in the main section, then this is at least visible when they look at the top or print the document.
A similar technique is to total all of the cells in a column, including the subtotals (if any) and totals, and divide by 2 (or 3, as appropriate) to see if the total at the bottom is the same as the divided-out total.
For me the best document on Spreadsheet design is “Spreadsheet Modelling Best Practice” which is downloadable from
http://www.eusprig.org/#DOWNLOADS
In the UK most of the large accountancy companies offer a spreadsheet auditing service, and there are also companies like Operis http://www.operis.com/
There are several professional auditing tool addins available:
http://www.spreadsheetinnovations.com/
http://www.uq.net.au/detective/
http://www.operis.com/oak.htm
http://www.bygsoftware.com/auditor/auditor.htm
Personally I have always refused to do any spreadsheet auditing work because I find its hard work testing my own spreadsheets, let alone anyone elses.
Charles
One thing I got out of this, looking at numbers all day is hard. After ten hours of excel, 3’s look like 8’s and 2’s look like 7’s. Sometimes when an error occurs it is so much easier to look at a symbol, like a smiley face, message or color indicating the error. I like the idea of incorporating an error check into the print document. If you have an error in your report, this is what is going to print. It forces the user to go back and fix his/her error.
I’ve started creating a single sheet dedicated to data validation / error checking.
The sheet is divided into groups of checks. eg. Group 1 for Sheet1, Group 2 for Sheet 2, etc…
Each group performs one or many checks within the workbook. eg. looking for blank cells, unmatched entries between sheets, hash totals, etc…
Each check evaluates to either TRUE or FALSE.
The entire column is conditionally formatted Pattern=Red for FALSE.
Each check is commented so that if it goes Red the user can read up about what’s causing it.
A tally at the top of the check sheet tells how many errors are detected – this too is conditionally formatted.
A resulting benefit is when one sheet affects another.
eg. Sheet1 gets fixed but in the process breaks Sheet2 – this is detected.
Rob
My spreadsheets can often be messed up if someone inserts a row or column in a critical area on a sheet. To prevent this, in (say) cell A1, i use the following formula:
=IF(ROW(Z100*COLUMN(Z100) 2600, “ERROR – ROW/COLUMN INSERT/DELETE DETECTED “, “”)
As it stands, the cell will appear empty, but if anyone does insert a row or column, the refernces will shift and the error message will appear.
And yes, I know best practise would be to design my sheets so that column inserts didn’t matter, but sometimes you need to compromise.
One way to prevent insertion of rows or columns in the middle of an important range, is to merge a 1 row x N column (or 1 column x N row) range off to the side of the critical range, which covers the rows or columns which must remain intact. You can hide the row or column with this merged range.
It’s the only use I have for merged cells.
I use one much like yourself – either returning a blank or “Data Good” when things are correct, but a “BAD DATA” or something like that which is conditionally formatted to turn HUGE RED BOLD if it happens…makes it impossible to miss.
There are quite a few expert Excel auditors around, aka software testers, and auditing tools, see my list at http://www.sysmod.com/sslinks.htm
There is a mail list discussing s/s design at
http://groups.yahoo.com/group/eusprig/
That’s the European Spreadsheet Risks Interest group,
who have a conference on this every year, see
http://www.eusprig.org
If I may be permitted a small commercial plug, I have a new book on ‘Spreadsheet Check and Control’ (ISBN 190540400X) which covers all those good points on cross-check totals and validations and more than forty other good practices. It’s being evaluated as courseware for a new ECDL (European Computer Driving Licence) certification, which is aimed at ordinary end-users, not professional developers.
http://www.SystemsPublishing.com
Expected availability is end-September. I have a few advance copies digitally printed that I am sending to reviewers in the print media.
I’m also running a one day course for people who want to learn how to audit spreadsheets, at Fort Worth, Texas, Sep 15.
http://www.sysmod.com/spreadsheet_auditing.htm
The timing was chosen just before the Excel User Conference, which is great value for anyone interested in developing their skills:
http://www.ExcelUserConference.com