This talk presents a great idea: relating spreadsheet characteristics to audit time. Operis audits many models every year, and David Colver has analyzed 75 of them to try to find out what predicts audit time the best.
File size is not a very good predictor, that makes sense, since some constructions like graphs add a lot of bytes, while not adding complexity. The number of unique formulas, and a simple measure of complexity (the number of unique formulas plus the number of used functions) didn’t work either.
So David tried some other metrics:
- Deep nesting
- The use of OFFSET
- The use of INDIRECT, David the CHOOSE function as an alternative to INDIRECT, that is an interesting idea
- The use of VLOOKUP, he argues that it is not robust, since inserting columns might break it. INDEX and MATCH are a better option
- Hardcoded values, although he also notes some values are okay like 365 and 1000
- The number of cell references
A nesting depth of 7 is a quite good predictor of audit time, if a spreadsheet has formulas with a nesting depth greater than 7, it will likely take more than average time to audit it. Interesting enough, INDIRECT was no predictor and OFFSET and VLOOKUP were only little ones. The number of constant values was a good prediction, more formulas with constants results in higher audit time. David did not remove the ‘okay’ values for this analysis, by the way. The total number of cell references in all unique formulas was quite a good prediction.
So, what do these results mean?
David answers the question why bad practice is so time consuming, by comparing the number of iterations and the hours spent on them. It turns out that bad practice is an indicator of weak models. Time spent on the iterations does not rise with bad models, but the number of iteration does. That means that the models under audit are not very good, not that they are necessarily hard to understand.
David ended the session with a rogues gallery of the worst formulas he encountered, always fun 🙂