ISERR

Application:

Imagine you are a teacher and you have a grade book for your students. You want to calculate their average score, but some cells might have errors for various reasons.

• A student might have been absent, so you entered "ABS" instead of a score, causing a #VALUE! error when calculating.
• A student might not have a score for a particular assignment, so you entered "NA" (for Not Applicable), which might result in a #REF! error or similar depending on your setup.
• You might have a blank cell where a score should be, which could also cause a different error.
• You might also have #N/A errors if you're using a lookup function like VLOOKUP to pull scores from another sheet, and it can't find the student's name.

You want to calculate the average score for each student, but you want to exclude any scores that are causing an error. However, you want to specifically handle the #N/A error differently, perhaps by just ignoring it, but treating other errors as a zero score for the purpose of the average.

The ISERR Function in Action

Let's look at the data in a table:

Here's how you would use the ISERR function to calculate the "Average Score" column.

For each student's row, you'd use an IF statement in combination with ISERR. The logic would be: "If the cell has an error (excluding #N/A), treat it as a 0; otherwise, use the actual value."

The formula for Ben's average score (in the "Average Score" cell for Ben) would look something like this:

AVERAGE(IF(ISERR(B2), 0, B2), IF(ISERR(C2), 0, C2), IF(ISERR(D2), 0, D2))

• ISERR(B2): Checks if the score in Quiz 1 for Ben (78) is an error. It's not, so ISERR returns FALSE. The IF statement then uses the value 78.
• ISERR(C2): Checks if the score in Quiz 2 for Ben (80) is an error. It's not, so ISERR returns FALSE. The IF statement then uses the value 80.
• ISERR(D2): Checks if the score in Quiz 3 for Ben (#VALUE!) is an error. It is, and it's not #N/A, so ISERR returns TRUE. The IF statement then uses the value 0.

The AVERAGE function then calculates the average of (78, 80, 0), which is 52.67. This isn't the desired outcome. The better approach is to use ISERR to handle the error values and simply ignore the #N/A values.

A more effective formula to get the result shown in the table:

IFERROR(AVERAGE(B2:D2), IFERROR(AVERAGE(IF(ISERR(B2), "", B3), IF(ISERR(C2), "", C2), IF(ISERR(D2), "", D2)), ""))

This formula is a bit complex, but it shows the power of the functions. A simpler, more direct application of ISERR would be to use a different column to clean the data first.

Step-by-Step with a "Cleaned Score" Column:

Let's add three new columns to the table to show the logic more clearly.