ISERROR Excel Function

ISERROR is a logical function which is used to identify whether the cells being referred to has an error or not, this function identifies all the errors and if any type of error is found out in the cell it returns TRUE as result and if the cell has no errors it returns FALSE as the result, this function takes a cell reference as an argument.

ISERROR Function in Excel

ISERROR function in Excel checks if any given expression returns an error in Excel.

ISERROR Formula in Excel

ISERROR Formula

Arguments used for ISERROR Function.

Value:  the expression or value to be tested for error.

The value can be anything such as a number, text, mathematical operation, or expression.

Returns

The output of ISERROR in excel is a logical expression. If the supplied argument gives an error in Excel, it returns TRUE. Otherwise, it returns FALSE. For error messages- #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL! generated by Excel, the function returns TRUE.

ISERROR in Excel – Illustration

Suppose you want to see if a number when divided by another number, gives an error.

We know that a number, when divided by zero, gives an error in ExcelError In ExcelErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more. Let us check if 21/0 gives an error by using the ISERROR in excel. To do this, type the syntax:

= ISERROR ( 21/0 )

ISERROR Illustration 1

and press Enter.

ISERROR Illustration 2

It returns TRUE.

You can also refer to cell references in the ISERROR in excel. Let us now check what will happen when we divide one cell by an empty cell.

When you enter the syntax:
= ISERROR ( A5/B5 )
given that B5 is an empty cell.

ISERROR Illustration 3

The ISERROR in excel will return TRUE.

ISERROR Illustration 4

You can also check if any cell contains an error message. Suppose the cell B6 contains “#VALUE!” which is actually an error in Excel. You may directly input the cell reference in the ISERROR in excel to check if there is an error message or not as:

= ISERROR ( B6 )

ISERROR Illustration 5

The ISERROR function in excel will return TRUE.

ISERROR Illustration 6

Suppose you just refer to an empty cell (B7 in this case) and use the following syntax:

= ISERROR (B7)

ISERROR Illustration 7

and press Enter.

ISERROR Illustration 8

The ISERROR in excel will return FALSE. Excel ISERROR function does not check for an empty cell. An empty cell is often considered as zero in Excel. So, as you may have noticed above, if you refer to an empty cell in operation such as division, it will consider it to be an error as it is trying to divide it by zero and thus returns TRUE.

How to Use the ISERROR Function in Excel?

The ISERROR function in excel is used to identify cells containing an error. Many times there is an occurrence of missing values in the data, and if the further operation is carried out on such cells, the Excel may get an error. Similarly, if you divide any number by zero, it returns an error. Such errors further intervene if any other operation is to be carried out on these cells. In such cases, you can first check if there is an error in the operation; if yes, you can choose not to include such cells or modify the operation later.

You can download this ISERROR Function Excel Template here – ISERROR Function Excel Template

ISERROR in Excel Example #1

Suppose you have the actual and forecast values of an experiment. The values are given in cells B4: C15.

ISERROR Example 1

You want to calculate the error rate in this experiment, which is given as (Actual – forecast) / Actual. You also know that some of the actual values are zero, and the error rate for such actual values will give an error. You have decided to calculate the error for only those experiments which do not give an error. To do this, you may use the following syntax for the 1st set of values:

We Apply ISERROR Formula in excel = IF( ISERROR ( (C4-B4) / C4 ), “” , (C4-B4) / C4)

ISERROR Example 1-1

Since the first experimental values do not have any error in calculating the error rate, it will return the error rate.

We will get -0.129

ISERROR Example 1-2

You may now drag it to the rest of the cells.

ISERROR Example 1-3

You will realize that when the actual value is zero (cell C9), the syntax returns no value.

Now, let us see the syntax in detail.

= IF( ISERROR ( (C4-B4) / C4 ), “” , (C4-B4) / C4 )

  • ISERROR ( (C4-B4) / C4 ) will check if the mathematical operation (C4-B4) / C4 gives an error. In this case, it will return FALSE.
  • If (ISERROR ( (C4-B4) / C4 ) ) returns TRUE, the IF function will not return anything.
  • If (ISERROR ( (C4-B4) / C4 ) ) returns FALSE, the IF function will return (C4-B4) / C4.

ISERROR in Excel Example #2

Suppose you are given some data in B4:B10. Some of the cells contain errors.

ISERROR Example 2

Now, you want to check how many cells from B4: B10 contains an error. To do this, you may use the following ISERROR formula in excel:

= SUMPRODUCT ( — ISERROR ( B4:B10 ) )

ISERROR Example 2-1

and press Enter.

ISERROR Example 2-2

ISERROR in Excel will return 2 as there are two errors, i.e., #N/A and #VALUE!Errors, I.e., #N/A And #VALUE!#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more.

Let us see the syntax in detail:

  • ISERROR ( B4:B10 ) will look for errrors in B4:B10 and return an array of TRUE or FALSE. Here, it will return {FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}
  • — ISERROR ( B4:B10 ) will then coerce TRUE/ FALSE to 0 and 1. It will return {0; 0; 0; 1; 0; 1; 0}
  • SUMPRODUCT (– ISERROR ( B4:B10 ) ) will then sum {0; 0; 0; 1; 0; 1; 0} and return 2.

ISERROR in Excel Example #3

Suppose you have the enrollment ID, name, and Marks of students enrolled in your course, given in cells B5:D11.

ISERROR Example 3

You are required to search for the student name given its enrollment ID several times. Now, you want to make your search easier by writing a syntax such that-

for any given ID, it should be able to give the corresponding name. Sometimes, the enrollment ID may not be present in your list; in such cases, it should return “Not found.” You can do this by using the ISERROR formula in excel:

= IF( ISERROR( VLOOKUP( F5, CHOOSE( {1,2}, $B$5:$B$11, $C$5:$C$11 ) , 2, 0) ), “Not present”, VLOOKUP( F5, CHOOSE( {1,2}, $B$5:$B$11, $C$5:$C$11 ), 2, 0) )

Let us look at the ISERROR formula in excel first:

  • CHOOSE( {1,2}, $B$5:$B$11, $C$5:$C$11 ) will make an array and return {1401,”Arpit”; 1402, “Ayush”; 1403, “Ajay”; 1404, “Dhruv”; 1405, “Mayank”; 1406, “Parul”; 1407, “Sashi”}
  • VLOOKUP( F5, CHOOSE( {1,2}, $B$5:$B$11, $C$5:$C$11 ) , 2, 0) ) will then look for F5 in the array and return its 2nd
  • ISERROR( VLOOKUP( F5, CHOOSE(..) ) will check if there is an error in function and return TRUE or FALSE.
  • IF (ISERROR( VLOOKUP( F5, CHOOSE(..) ), “Not present”, VLOOKUP( F5, CHOOSE() )) will return the corresponding name of the student if present else it will return “Not present.”

Use the ISERROR formula in excel For 1403 in cell F5,

ISERROR Example 3-1

it will return the name “Ajay.”

ISERROR Example 3-2

For 1410, the syntax will return “Not present.”

ISERROR Example 3-3

Things to Know about the ISERROR Function in Excel

  • ISERROR function in Excel checks if any given expression returns an error
  • It returns logical values, TRUE or FALSE.
  • It tests for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

ISERROR Excel Function Video

Recommended Articles

This has been a guide to ISERROR Function in Excel. Here we discuss the ISERROR Formula in excel and how to use ISERROR in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *