The one who has never made a mistake is dangerous.
(Book of the Samurai)
Mistakes happen. It’s doubly insulting when they happen through no fault of yours. So in Microsoft Excel, some functions and formulas may give errors not because you messed up when entering, but because of a temporary lack of data or copying formulas “with a margin” to redundant cells. A classic example is the divide-by-zero error when calculating the average:
And note that the totals in our table are also no longer considered – one error begins to generate others, passing along the chain from one dependent formula to another. So because of one erroneous cell, in the end, the whole calculation may stop working.
To treat such situations in Microsoft Excel, there is a mega-useful function IFERROR (IFERROR), which can check the given formula or cell and, in case of any error, return the given value instead: zero, an empty text string “” or something else.
The function syntax is as follows:
=IFERROR(What_check; What_to_output_instead_of_error)
So, in our example, we could fix everything like this:
Everything is beautiful and there are no more errors.
Note that this feature has only been available since the 2007 version of Microsoft Excel. In earlier versions, you had to use the functions EOSH (ISERROR) и END (ISNA). These functions are similar to IFERROR, but they only check for errors and do not know how to replace them with something else. Therefore, it was necessary to use them necessarily in conjunction with the check function IF (IF), creating nested constructs like:
This option is noticeably slower and harder to understand, so it’s best to use the new IFERROR function if possible.