How to manage excel errors with the iserror function

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

It’s a common problem but one that often seems difficult to manage. Last week, we discussed the use of “iserror” when doing multiple conditions and that certainly seemed to be of interest. Unfortunately, excel is not very good at managing errors. Try to do a vlookup on a value that excel cannot find and not only will you be unable to do anything with that cell but any reference to that cell will turn out as an error as well. It can become very frustrating and can make things very difficult to manage.

While it’s not always the case, I personally usually prefer when excel can keep the calculation and the spreadsheet working while possibly giving me notice of the error at the same time. A challenge? Not necessarily. Today I will discuss the use of the “iserror function” and how you can use it to keep things working while still seeing that there are possible issues.

First off, let’s take a look at a possible spreadsheet that would require the use of the iserror function

Imagine a company that has a sales team. For each of these sales people, they give a different commission rate. At the end of the month, they would want to easily know how much sales were made and how much commission should be paid out. However, using the vlookup function would generate errors each time a new sales person is added. While it’s practical to have a “notice”, it’s not always ideal to have to correct all of those before getting an estimate, especially in a big company. Take a look at the following spreadsheet

What could be done? One possibility would be adding the missing sales team members on the left and we can all agree that it will need to be done at some point. However, a short term solution could be setting a “estimate of 7% commission” for missing members. How to do it? Instead of using the straight vlookup such as:

=VLOOKUP(D2,A:B,2,FALSE)*E2

I will use an “iserror” such as:

=IF(ISERROR(VLOOKUP(D2,A:B,2,FALSE)*E2),7.5%*E2,VLOOKUP(D2,A:B,2,FALSE)*E2)

What will it look now?

Much cleaner right? And it gives a decent estimate of the commissions to be paid out. The problem of course now is to find those errors. How to do it? I would personally create a column just on the right to detect errors by doing the following:

=IF(ISERROR(VLOOKUP(D2,A:B,2,FALSE)*E2),”MISSING”,””)

You could make it even more explicit by adding conditional formatting:

And it looks much better:


Any thoughts? I’d love to get your comments on this!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.