MVP #9: The powerful use of ISERROR in Microsoft Excel

Hi!

In this post I will show you the amazing use of ISERROR in Microsoft Excel.

Take a look at the screenshots below

Here is the background gist.

This is the previous month sales record of a store in Nigeria. Garri being the poor man's manna: can be eaten with beans in the morning, soaked in the afternoon and eaten as Eba at night. 100 bags of it were sold, for $10,000 aggregate.

Then Rice, being Nigerians' favorite staple and the king of all parties, had 150 bags sold and garnered $300,000. Beans, also being the favorite of some weird Nigerians, had 50 bags sold, totaling $30,000.

Finally, Uncle Jonathan has been too busy to remember the Cassava Flour campaign. Hence, no one ate cassava bread and no one bought cassava flour. Quantity sold = 0. Sales = $0 too.

In the second snapshot, you see the formula used to calculate the Price per quantity sold. And it's giving an error (as shown in the first snapshot).

So how can you fix this?

Easy. Use ISERROR

And voila! Problem fixed.

Now here is the technical garbage.

ISERROR is an Excel function that accepts only one argument in the form ISERROR(xxx).

It returns the logical value TRUE if the argument resolves to an error or FALSE if not.

Example, if you do =0/0 (as in the case of Cassava flour), you get an error - #DIV/0! 

If you do =ISERROR(0/0) you'll get TRUE (a logical value).

So when you do =IF(ISERROR(C5/B5),0,C5/B5) you are saying - if ISERROR(C5/B5) evaluates to TRUE then return value, 0. Otherwise, return the real value of C5/B5.

This is just a simple example. There are more powerful ways of using ISERROR, e.g. to some values in a column that is bugged with errors with having to edit anything in the original data.

Feel free to contact/hire me via the comment box.