Adding IFERROR to Your Excel Diet

There are a lot of Excel tips and tricks out there---whole blogs, YouTube Channels, message boards, and more devoted to all of the things that make Excel such a versatile piece of software. While the purpose of this blog is not to replicate all of that amazing content, I do want to pull out ideas and functions that educators might find the most useful.

Have you ever set up an equation in Excel and gotten an error message---such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!? Total buzzkill. On one hand, these alerts serve a greater good. They let you know if your formula has gone awry. And, on the other hand, they can show up in an embarrassing places.

In our Roll Your Own Gradebook series, we made the assumption that we had classes of Stepford children: every student completed every assignment. But let's face it, that's not what really happens. For example, let's say that Flash Gordon wasn't enrolled in the course for the first quarter. If there are no scores, then Excel gives us an error message when it tries to apply the formula:

Uh-Oh, Spaghetti-O

You can change this, without altering the outcome for other students, by using IFERROR. This function tells Excel to evaluate what's happening, placing one value in the cell if there's no error (e.g. score for Stepford child) and another if there is an error message (e.g. "Trash" Gordon, lazy athlete). In short, it allows you to bypass the error message.


How It Works
You could apply the formula to more than one location in the gradebook and get the same result, but for now, let's look at the worksheet with the scores.

Superhero Behaving Badly

The cell on the right---the one with the "###" is the one we need to address. The current formula is =MEDIAN(H14,J14). Alas, there are no scores to find the median for---hence the error.

Instead, we can use =IFERROR((MEDIAN(H14,J14)),"") The double double-quotes at the end tell Excel to leave the cell blank. However, you can put another value there or even a text string (e.g. "No Grade"). Here is what we see now:

Ahhh...That's Better

What do we see on the Dashboard?

A blank. Wow. Excel is doing just what we told it to do. Imagine that.