EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR

Excel is great for being able to apply the same calculation consistently by simply copying the formula to all of the relevant cells, however often there are times where some of the data isn't how we would like it and the formula throws up an error.

The most common of these is the #DIV/0! error that Excel returns when trying to divide a number by zero. As Excel struggles with the concept of infinity it returns this error. A common situation where this occurs is in a Gross Margin Percentage calculation. We may have multiple products with sales and profit figures and a formula to calculate the margin as a percentage, i.e. profit/sales formatted as a percentage.

This will work fine until you come to a product with no sales where the formula will return #DIV/0!.

This can be addressed using a combination of the IF statement and ISERROR (note that in Excel 2007 and 2010 there is a combined function called IFERROR, however the solution proposed here will also work in earlier versions of Excel). If you need a refresher on how the IF statement works take a look at :

EXCEL TIP: The IF Statement made simple

If cell A1 contains the Sales figure and B1, the Profit, then to show the Margin Percentage in C1, we would format it as a percentage and use the following formula:

=B1/A1

This will return the #DIV/0! if cell A1 is empty or zero.

The ISERROR function returns TRUE if its argument returns an error and FALSE if not, therefore

=ISERROR(B1/A1)

will return TRUE when this #DIV/0! would occur.

We also need to decide what we want to appear instead of the error. In this scenario, I usually show 0% which is what I have assumed for the example.

Using the ISERROR formula above as the condition for the IF statement, we can return zero if it is an error, or the original calculation otherwise. So C1 becomes:

=IF(ISERROR(B1/A1),0,B1/A1)

No more #DIV/0! errors!

A similar approach can be used for any other common errors, such as when a looked up value is not in the list.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

EXCEL TIP: Using the SUMIF function to interrogate lists

How many times do we need to pull out a particular category of numbers from a list and add them up. Well, you might not be aware that Excel has a pretty simple function designed to do just that.

The SUMIF function can be used to pull out departmental information from a trial balance, sales information from a transaction list for a particular salesman, department or division (as in the example we will use), or for any other situation where you have a list and need to selectively sum a subset of that list based on criteria.

It is actually a pretty simple function when you know how it works, but is very powerful in the types of situations outlined above.

See below the example situation we will be using to illustrate this function:

This is a simplified situation where we have a list of sales transactions in columns A to C (maybe imported from your accounting software) and want to report total Retail and total Wholesale sales.

We will use SUMIF to return the values in cells F3 and F4.

The format of the SUMIF function is as follows:

=SUMIF(CriteriaRange,Criteria,SumRange)

where CriteriaRange and SumRange are one column wide and the same number of rows as each other in height.

CriteriaRange is used for the column that includes that data that you wish to filter your sum by (in our example, C2:17).
Criteria identifies which item in the criteria range column you want to sum (in our example, "Retail" or "Wholsale" for cells F3 and F4 respectively).
SumRange is used for the column that holds the data you actually want to add up (in our example, B2:B17). This argument is optional and if not entered CriteriaRange is used.

So for our example, cell F3 contains:

=SUMIF($C$2:$C$17,E3,$B$2:$B$17)

Notice I have used the dollar signs to fix the ranges and used the reference to cell E3 rather than the word "Retail". This can then be copied down to cell F4 to become:

=SUMIF($C$2:$C$17,E4,$B$2:$B$17)


The criteria does not need to be a simple matching as in our example. A condition such as ">0" could be used to sum only positive values (this is an example where you might not enter the separate SumRange as the criteria is applied to the data being summed.

With our example data, the formula:

=SUMIF($B$2:$B$17,">1000")

returns £1,431.

Have a play with it and see what you can use it for.

SUMIF is great if you know the exact format of the output that you want and you require a fixed layout. If you want to summarise all of the data in your list dynamically, a pivot table might be more what you need.

If you want this kind of help on hand when you need it, have a look at my Excel Advice by Email service which provides email advice when you get stuck for a small subscription.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Need a Spreadsheet?

Many readers have used my Spreadsheets by Email service when they have wanted a spreadsheet built to their requirements, so I thought I had better ensure that readers were aware of some significant changes to the presentation of the service (no changes to the quality of the work, or who carries it out).

As the service is very useful both to those who know Excel well and need a little help (I suspect this is the case for most readers of this blog), and people who have never opened a spreadsheet in their lives - I was concerned that the website might have intimidated some of this second group if it came across too "geeky".

So, I've gone for a complete change of name and presentation, which I think will appear more accessible to all. And now you know where I've disappeared to for the last few weeks.

Welcome to needaspreadsheet.com.

Please take a look and let me know what you think, and if you have a spreadsheet you need building, please fill in the form on the site.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.