Excel IFERROR Function

The ‘IFERROR’ function in Excel will return a set value (which you select) when a formula generates an error. If no formula error is returned, then the normal formula output is shown.

The IFERROR function will take effect if any of the following errors occur; #N/A, #REF!, #VALUE!, #NUM!, #DIV/0!, #NAME?, or #NULL!.

When dealing with large data sets where you are copying formulas across numerous rows (e.g. if calculating the gross profit margin for a number of products which may or may not have sales in each period), it can be useful to overlay the IFERROR formula to avoid seeing numerous #DIV/0 errors.

Types of Excel errors:

  • #N/A – The #N/A error is returned when the numbers you are referring to within your formula cannot be found. For instance, if the cell you are trying to lookup in your =VLOOKUP formula cannot be found within the specified cell range, an #N/A error is returned.
  • #REF! – The #REF! error is returned where you are referencing a cell that doesn’t exist. For instance, the formula =A1+B2 would return a #REF! error if column B was subsequently deleted.
  • #VALUE! – The #VALUE! error is returned when it finds text or other characters where it expects to find numbers. For instance, a value error would be returned if you tried to add separate cells containing 7 + G (or any other letter).
  • #NUM! – The #NUM! error arises when there is a problem with a number in the formula. For instance, if you apply =LARGE() to a cell range with 4 numerical cells, and ask for the fifth largest number, a #NUM! error will be returned as the fifth largest number does not exist.
  • #DIV/0 – The #DIV/0 error is returned if you have attempted to divide by zero.
  • #NAME? – The #NAME? error is returned if you have attempted to refer to an incorrect named range.
  • #NULL! – The #NULL error is less common as it typically only occurs where you specify an intersection of two areas which do not actually intercept.

Syntax

=IFERROR(value,value_if_error)

Arguments

  • Value – The cell reference or formula you want to check for an error.
  • Value_if_error – The value you want to return in the event of an error. If you do not wish for a value to be returned, enter “”.

Function category

Logical

Returns

A set value (which you select) where a formula generates an error.

Download Excel File

 Example of IFERROR function
error

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.