As I was scrolling through the index for my book recently, I noticed the #REF error jump out at me. Why is it that the #REF strikes such fear into a modeller’s heart? Probably because out of all common Excel errors, it is the one which is the most difficult to fix. It means that your formula is referring to a cell that doesn’t exist anymore! If someone has saved a model with a #REF error in it, it’s not very easy to correct because the formula that it’s referencing has gone; so the formula needs to be completely rebuilt.
As annoying as they are, I actually like seeing Excel errors in my formulas believe it or not, as it means that something isn’t working, and therefore I can fix it. As I always tell my training course attendees – the only thing worse than finding a mistake in your model is NOT finding a mistake in your model and your model being wrong! Worse than that is your boss finding the mistake in your model. There’s no faster way to lose credibility as a modeller than for others to find formula mistakes in your model, and there are a number of strategies to reduce error that you can employ reduce this possibility in your modelling.
If you’ve attended one of my courses, you’ll know I’m quite fond of the SUMIF function is it’s much more versatile and robust than other popular functions such as a VLOOKUP. One of my issues with the SUMIF though is that it does not warn you if you use mismatching ranges. In the example below, the result in cell C13 will not return an error; it will simply give a wrong answer without any warning!
Source: Fig 6.6 on page 96 of Using Excel for Business Analysis
The thorough modeller in this example will check the totals as they work, and then put an error check below in cell C17 to make sure that any incorrect entry in the future is quickly identified.
Even better than the SUMIF is its big brother, the SUMIFS function which can handle multiple criteria and – critically –will warn the user if the ranges are mismatching, unlike the SUMIF. It’s one of SUMIF’s annoying little quirks that it allows mismatching ranges and exactly why SUMIF allows this, is something I’ll never understand. It’s highly unlikely that we’d deliberately want mismatching ranges and far more likely that it’s been done inadvertently (although I have been known it use it for staggering periods).
So instead of cringing at a horrid #VALUE! error value, or supressing it with an IFERROR function, we should instead embrace, use and perhaps even love our Excel error values! Figure out what’s wrong, and get to the bottom of it. An Excel error value is far more helpful than the alternative which is to have deceivingly innocent number values which are in fact incorrect.
