Hi, this is Wayne again with a topic “The Excel IFERROR Function: Clean up Your Excel Errors”.
In this excel video we’re going to learn how to use the if error function, to clean up your data in excel and prevent error messages from ruining your formulas and your data. So here we have a spreadsheet, it’s an employee list and, let’s say i’d like to figure out the sales efficiency. Let’S call it of each employee how many sales have they generated, taking into account the number of days that they’ve worked for the company this year. To do this, i can just click here in cell f2 type equals and i simply want to divide the number of sales by the number of days worked. So i can type d2 or i could click on d2 either way divided by which is a forward.
Slash e2 tap enter and here’s the number that i’m going to call sales efficiency now to make this a little bit easier to read, i’m just going to click and drag and actually highlight the entire column pretty much down to here and then i’m going to go Up here to the home tab in the number group, i’m going to click this button here to decrease the decimal points that are shown how about just two okay great now, i can apply this same formula all the way down the spreadsheet, just by double clicking on The autofill handle the little green square in the lower right hand, corner of the cell that i have selected so i’ll double click there. It copies it down the sheet updating the cell references so that it’s always dividing the cells that are to the left of the cell in question, but notice that we have some errors. Here. We have a div, 0 error.
You can see that there are three instances of that error and you can figure out what this means dividing by zero right. So here we have someone that hasn’t started yet as an employee for the company they still haven’t been hired, yet so zero sales divided by zero days worked, and that gives me an error message same with these other two individuals down here now. Is that even a problem? Well, it may not be, but what if i wanted to calculate the average sales efficiency here in this cell, i’ll type, equals average left parenthesis and then i’ll just click and drag to select everything that i want to average tap enter. And again i get an error message: why? Because it’s pulling data from above where there are errors, and so the error is perpetuated and i get that same div 0 error message in my average formula. So let’s use the. If error function to solve this problem, i’m going to go up here to f2 click on the cell and i could double click on it to edit my formula, but in many cases it’s just better to use the formula bar up here to edit your formulas. So i’m going to click here right after the equals sign and type.
If error left parenthesis, you can see what excel is expecting they’re expecting a value of some kind, and what we have here is a value d2 divided by e2, so i’ll just go after the two put in a comma. Now i’m supposed to put in the value to display, if there’s an error in this case, a zero might be the best thing to put there. I should put in my right parenthesis tap enter and it looks like nothing changed, but the formula did change and if i double click now on the autofill handle to copy the formula down the spreadsheet, look, what happens the error messages are removed.
Instead, we just get zero and look at our average formula. It’S now able to calculate the average efficiency because, instead of having error messages to deal with it, just has the number zero. Now it is possible instead of having a number like zero put into the cell. It is possible to put text so i’m going to go up to the formula bar erase the zero and i could put in a quote because this is text it needs to be in quotes, and i could type in a message like hasn’t started yet making sure. I end with a close quote: tap enter on the keyboard, i’ll click on the cell. That has the formula that i just edited and then double-click on the autofill handle.
If you don’t want to double-click, you can also click and hold and drag down the page. It will give the exact same result in this case, so i drag it down the spreadsheet release the mouse button and now, instead of showing zeros, it says, hasn’t started yet, which is nice now notice that our average formula still appears to be working. But the number is a little different, so that is something we’ll have to watch out for and be careful with. But it is nice that you have the option of replacing an error with a number or with text i’m going to undo all of that just by using control z.
I want to go back to the errors, because i want to show you one more example of how you could use the if error function in excel, so here where it says: average efficiency, i’m going to click on the cell. That has the formula that calculates the average efficiency and i’m going to go up here to the formula and i’m going to leave the formula as it is, except that i’m going to wrap it in an if error function so after the equals sign i’ll type. If error left parenthesis i’ll leave everything else, the way it is, let’s look at the hint from excel. If error value, i’ve got my value already, so i’ll click after that put in a comma. What do i want the value or the text to be? If there’s an error in this case, i’ll just put in quotes the message check, errors above close quote, right parenthesis tap enter and, of course my message could have been anything else that i chose, but i hope you can see how this is a little different than What i showed before in my first example, we used an iferror function, just with a simple value and turned it into a zero in this case, and so that’s nice and it’s effective and powerful. But the formula only includes one function. If error, in this case below, i took an already existing formula that had a function average and wrapped it in an if error formula, so both of those methods should come in handy as you try to prevent the effects of errors in your microsoft excel documents. Thanks for watching, i hope you found this tutorial to be helpful.
If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you can support me through my patreon account or by buying channel merch and you’ll, see information about those options in the description below the video .