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.

A business plan from my 12 year old son!

I received the following email from my 12 year old son this morning. I was at work, and he was home, obviously putting his half-term holiday to good use!

Subject:  I know how to make over £30.00 at school in 6 weeks

"Everybody is interested in tech decks at school and they all say the cheapest place to get them is crazy clearance. They are apparently £3.00 each at crazy clearance. I have found a pack of 10 tech decks for £14.43 which is about £1.44 each. If I sold 10 per week at £2.00 I would earn £20.00 and make a profit of £5.57 per week. If I could do that for 6 weeks I would have made a profit of £33.42 towards my TV. Is this a good idea. Also do you think offering a free lollipop from the shop would be a good idea if I couldn't sell any in my first week because I would still make a profit on each tech deck. In east lee stores the lollipops are 20p and were popular at the beginning of the year, being sold at around 30p. If I charged £2.00 for a tech deck and a free lolli It would cost me £1.64 per tech deck and I would make a profit of 36p per sale. I would not do this straight away however because without the the lolli I would make a profit of 56p per sale. That is a 28% profit margin which is quite decent. Please tell me if this is a good idea. The only thing is allot of people are selling used tech decks for £1.50 but with mine they would be paying for the quality. I think it is decent because it means more than doubling my starting budget which would have to be £15.00. Tell me if this is a good idea and if so could you please build me a spreadsheet when you have the time just to keep you practicing your spreadsheet skills. And don't charge me for the spreadsheet because I can do it myself."

Putting aside the typos and lack of paragraphs (and the cheeky jibe about Spreadsheets by Email at the bottom), I realised I had come across so many business plans from grown-ups that weren't as considered.

He has sourced a supplier, researched the market (even establishing that he can still cover his costs at the current rate for second hand product) and knows his margins. He has even given thought to a promotion!

Part of me feels incredibly proud, and part of me worries I've created a monster!

By the way, I had to ask what a Tech Deck was, and this was his reply:

"It is a mini skateboard that you can use your fingers to ride and do tricks with. You can also get mini skate park equipment to use it on. Tech deck is the brand name though. you can get cheaper ones but they break alot easier."

What's more, this second email had a link to his blog in the signature!

It sounds like he'll do his bit for all of our pensions!

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.