Excel has an all-purpose error handler function called IFERROR.
If you create a formula and then realise it needs to handle errors, you need to insert the IFERROR function at the start of your formula.
Let’s review this simple formula.
=A1/B1
If cell B1 contains zero, then the above formula will return the #DIV/0! error.
The IFERROR function can wrap around the above formula to handle a zero value in B1.
=IFERROR(A1/B1,0)
The IFERROR has two arguments, or parts. The first argument is the calculation to perform, and the second is what to return if the calculation returns an error. If the calculation doesn’t return an error, then its result is displayed. If the calculation does return an error, the second argument is displayed.
IFERROR handles nearly all Excel’s formula errors. If A1 has a #VALUE error, the above IFERROR function will still display 0. The IFERROR function can, to some extent, mask or hide errors because it handles all errors the same way.
In older Excel versions, IFERROR handles all errors. In the subscription version of Excel, IFERROR doesn’t handle the #SPILL error, which is related to dynamic arrays.
Inserting the IFERROR function into existing formulas can be time-consuming and repetitive, my two main triggers to consider creating a macro solution.
It is important to keep in mind that macros clear the undo list, which means they cannot be undone. This also means you can’t undo anything you did before you ran the macro. Save your file before using a new macro, and then close the file without saving if a problem occurs. Alternatively, test the macro on a copy of the file.
Brought to by Neale Blackwood https://twitter.com/ExcelYourself