Logical formulas IFERROR()

This is probably the most useful formula to be included in excel in recent years.iferror 20160801

There are often occasions when you have a perfectly valid formula that results in one of Excels Standard errors (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). Whilst these error responses are perfectly correct they will affect the results of other cells that are dependent on them. Additionally to a lot of people eyes they are visually disruptive. The IFERROR formula is used to trap these errors and provide an alternative result – often zero.

IFERROR is structured thus: =IFERROR( formula, alternative result). The alternative result can be another formula but in my experience it is more often than not set to zero.

In the example on the right the result with the IFERROR formula can be shown in column G – the result of +F5/D5 for products 11 and 12 results in a #Div/0! Error as there is no budget.

In Column I the error trapped the error using the IFERROR formula and replaced with no budget (the full formula is shown in the formula bar.

%d bloggers like this: