Iferror Function

What do you do when your formulas return error values for your calculations, your reports are filled with error values and dependent formulas messed up? Here is a quick and neat solution for that problem. It is called iferror function.

Iferror function replaces a cells value with another value that you specify, when original value is an error. If your calculation includes a division by zero or such, you will get an error value as result. But if you nest that calculation into an iferror function, you will get the value you specified, instead of some error.

I’m aware that you can always fix these kind of issues by manually editing those cells. But one doesn’t always works with a small list of calculations. Imagine your list has 10.000 rows with multiple errors in multiple columns. That manual correcting will be nasty right? So it is better to think systematically and make your calculations error-proof.

Iferror function is pretty easy to understand. Lets say you have a formula like below:

=sumif(A1:A100;B1;C1:C100)

İf you want this formula to be error proof, all you need to do is this:

=iferror(sumif(A1:A100;B1;C1:C100);”0”)

In plain english; =iferror(your value; replacement value)

(Note that you can specify a formula instead of a value as if_error value.)

Now if your calculation results with an error, it will return “0” instead of an error value.

Here is another example application of iferror function:

Lets assume that one of your sales performance indicators is sales per customer. And this value is calculated by dividing sold item quantity with customer quantity for every day.

When there is at least one sales transaction and one customer everyday, things will go smoothly. But when there is no customer for one day, your sales per customer calculation will result with an “division with zero(#DIV/0!)” error. This will look bad on your report for sure. Here is how it will look:iferror (1)

 

Now lets nest our calculations in row 5 inside an iferror function. For this example I specified the if_error value as “No Customers”. So whenever my calculations on row 5 results with an error, “No Customers” will be shown instead of “#DIV/0!”. Here how it looks:

iferror (2)

 

You may also like...

Leave a Reply

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

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