7 thoughts on “Spreadsheet Errors”

1. I, of course, never make mistakes – but Excel makes *hundreds*!!

2. Any experienced Excel developer will have (if they’re honest) a “Hall of Shame” of the mistakes made.

   A more interesting question (which you partly touch on, John), is how to put together a structured testing & debugging process which traps as many material design, logic and input errors as is feasible.

   Here, it matters a lot what kind of spreadsheet you’re building. Some can be thoroughly tested in a matter of minutes/hours with a calculator and a cup of coffee (a peer is also helpful). Others, that run into thousands of lines of code, extensive VBA, and multiple megabytes, require something more industrial grade.

   Like John, I’m interested to know if anyone out there is very proud of their industrial grade error trapping process.

3. > Like John, I’m interested to know if anyone out there is very proud of their industrial grade error trapping process.

   Anndrw,

   I work in the business evaluation section for a top 50 ranked company, we build evaluation models for internal projects, M& A etc . We have a well established and tested process for audit and review – but we aren’t complacement, we know all too well from the external models that we see that it only takes one significant mistake and we would do great damage to our internal name.

   Here is a brief list of our techniques, no rocket science, but it has stood us in good stead

   – always add a intro page
   – stick to a set corporate modelling architecture and cell colouring system, people need to be looking at the data and model drivers, not spending their time working out the model structure
   – transparency is king. Break long formulas up into multiple lines. Add text descriptors to cells rather than comments that aren’t always as visible
   – a modellers intelligence is shown by making a complex issue look simple, not by building ridiculously long formulas
   – no VBA (well, at least in 99.9% of situations. And even in that 0.01% disabling macros shouldn’t cause the model to fall over or be rendered unusbale)
   – no array formulas
   – no circular references (meaning deliberate ones)
   – no hidden sheets, rows, columns etc
   – no white font on white background
   – range names can be more pain thann they are worth (Hope you don’t read this Bob)
   – use the Metadata to comprehensively describe the model to assist with file searches
   – put totals and checks on the LHS of a model where they can be easily seen. Too many developers put them in Column AS or similar where they fall off the radar screen
   – keep clear of more complex designs that are harder for the average user to follow, the use of OFFSET ranges comes to mind
   – do use conditional formatting and data validation
   – use graphs for input data, output data and trends. Very basic, but greatly under utilised
   – use inhouse and professional tools for audits. The best external tool I’ve used to date is Spreadsheet Detective.
   – always get your model audited, preferably by an uninformed user with a comprehensive audit template in hand
   – run Microsofts Hidden Data tool on your model for two reasons. (1) picking up errors (2) sanitising models for external consumption
   – add checks & balances where appropriate. When calculating a key metric do it via a couple of methods (ie NPV using year on year manual factors as well as by XNPV)
   – never release a “draft” result, no matter who asks for it. No matter how many caveats, disclaimers, warnings and WordArt you use, the number will become set in stone and you will be reconciling to it using waterfall charts for what seems like all eternity.

   I’m keen to hear the methods you and others use as well

   Dave

4. …. and last of all, use spell checker so you don’t look a knob for spelling Andrew as Anndrw

5. Thanks for the detailed response, Dave, (or Dive, or….)

   I see spreadsheet audits as having two components, done in sequence: a good practice review, and a detailed input/code/output test. If a large and complex spreadsheet uses poor practice, then a detailed review is futile – you just can’t do it efficiently. A good practice model can, in principle, be tested efficiently. However, I have come to believe that there are genuine trade-offs involved: it’s impossible to guarantee that a large and complex model is 100% free of errors or bugs. The question becomes: how much resource do you throw at it, for how long, using what checks, and when do you say good enough?

   Going to your list of practices:

   a) intro/readme page – Check
   b) architecture/formatting – Check
   c) simple formulas – Check plus. In our modeling classes, we talk about the “Coding stud” – a 40-ish guy in a polyester suit given to pick-up lines like “see my eight line long formula, baby”
   d) no VBA – sorry, sometimes VBA is the only way to go. I’m not a great fan of custom functions, but there are high end tasks which you can only do by writing (and testing and debugging, and documenting) a good set of algorithms.
   e) no array formulas or circular references – Check
   f) no hidden sheets, columns or fonts – Check
   g) no Range Names – we like them. Used in the right way, they make your code self documenting, which is a really nice plus when you’re auditing someone else’s code.
   h) use Metadata – that’s new to me.
   i) put totals on LHS – disagree. Make calculations flow front to back, top to bottom and left to right (English language convention). For wide calculation blocks (going say from column D to column AS) create a column group for columns E to AR (use grouping settings so the + button is on the left). This creates the really nice effect that you can check the code in the first and last columns to verify that you’ve written the same formula (range names again!), and makes it easy to follow the logical flow.
   j) steer clear of complex designs – mostly, yes. We put a premium on simplicity. This sometimes requires the use of intermediate/advanced functions. Occasionally, an elegant piece of advanced code clears up a lot of real estate. You have to document it, though.
   k) conditional formatting and data validation – Check.
   l) use charts – Check plus. I howl and tear my hair when I see a 3MB model over 15 spreadsheets with two (count’em) charts.
   m) use tools for audits – we use JWalk’s PUP5 (hi John!), but not Spreadsheet Detective. I had hoped to get more out of FastExcel (seems to crash on big models) and I’m running my eye over a Compassoft product, Exchecker.
   n) always get your model audited – we’re working on how to formalise and structure this.
   o) Microsoft’s Hidden Data tool – huh? I was not aware of this. Sounds interesting…
   p) checks and balances – er, Check plus (no pun intended). Do logic checks and reasonableness checks (does this kind of project usually come with a 50% IRR? Ya think?)
   q) never release a draft result – sounds like you’ve been burned on this.

   One thing we’ve tried to introduce is a user/developer paradigm, where the developer may be one of several people who use the model in earnest. We like multidisciplinary teams – they sometimes ask questions which surface great insights, and being able to use and run a model is a powerful way to do this.

   Sometimes this surfaces errors – which is a good thing.

   This has forced us to pay attention in our model architecture to separating the User Interface from the Calculation Engine. Users don’t need to be checking or testing formulas, but they do need lots of places to ask “what if” questions and lots of juicy charts and tables to see what happens.

   So, long response to a long response. Looks like we share many of the same practices, with just enough areas of polite disagreement to make things interesting.

6. Hi everybody.

   I bow to all the Excel Gurus !!!!!!
   Do have a look at the following link. May be of help. I am just a starter in excel.

   http://www.spreadsheetinnovations.com

   Regards
   Kanwal
   (India)

7. I pretty much agree with Dave/Andrews list.
   I prefer to avoid VBA where possible too.
   Not a big fan of too many range names either, a few is ok.
   I am a bit of a fan of checks and totals left and top – its not usual in spreadsheets, but in many other places the most important stuff goes top left.
   I’d add an index sheet if the model is more than a few sheets (theres a free add-in on the codematic site to add a hyperlinked index sheet)
   tools – Exchecker is good, as is SSPro, and SS detective there are plenty of audit tools out there with more being released all the time – be sure to search.
   Excel XP also has some error checking which can be useful.
   cheers
   Simon

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.