Avoid the #DIV/0 error message

- [Instructor] On this worksheet we've got some sales in column B. Now, some of them are formulas and we've got a division by zero entry here. And what does that mean? As I double click to expose the formula. Even though we're not seeing the data, it means that cell P9 has a zero in it. You can't divide by zero. Now, we've got some other errors here too. Maybe it's a big list. We don't have time to clean these up. We do want a total out of column B. We'd like to ignore those errors. So, in cell E3, we're trying to do that.

We're trying to add up all the data in column B and so, we get that error message that means we can't divide by zero, so we can't total these up but we'd like to. Now, by using a various combination of if functions and iferror functions, we could possibly come up with a formula here. But it's gonna be a lot easier to use a completely different function called aggregate. As soon as you begin typing it and see it, you can simply double click it into place and look what we see here. A list of 19 different functions that we might wanna consider using.

The first 11 or so are actually alphabetized, so later additional functions were added here. We want to simply sum and that's the number nine. If you use this frequently, you'll just put in the nine, we can certainly do that here or double click to edit. Then look what happens as we press comma. There are eight different options here. Zero through seven. The one we care about here is ignoring error values. We'll simply put in the six. Do look at some of the others though because at different times you might wanna consider using them.

If you are familiar with the subtotal function and you become familiar with aggregate, perhaps you'll use som of these other options. We'll use the six right here. Comma and then we indicate what it is we're trying to tabulate. I'll simply click column B and press enter and we have an answer. If we wanted to check that out, I'll highlight the cells from B2 down to B8, let go of the left mouse button, use the control key, highlight these and these too, each time I'm using the control key and highlight these.

As we look in the status bar at the bottom of the screen, we see if sum is exposed. 24,666.00 and that's what we're seeing here, so it is a verifier. And of course, if we wanted to do these here for an average max and min, I'll copy just one of these downward. We'll need to make an adjustment here because if it's an average we'll use a one instead of a nine. And when you forget this, you might have to delete this, put in that left parenthesis and then the options will pop up, so it's a one for average and so, we have an average.

So, we can work around the errors but of course the long-term adjustment is clean up the data but we can work around the errors by using the aggregate function here. Another worksheet here called Profits, no errors at the moment but we got a potential problem if, for example, in one of these months, the sales and expenses happen to be the same. Because in row 12, we're tracking percent of profit change using formulas like this. We're subtracting the two profit cells and then dividing by the earlier entry.

Well, what if, in my case, say April here, what if the expenses were 240? That means the profits are zero. Look at what we have happening here. We're trying to divide by zero. Can't do it. Now, in one sense, that's okay. It exposes the error and maybe that's the way we want it to be. But if you're making a presentation of numbers, maybe you'd just rather not see that. You can certainly explain it if you want to, so how can we work around this and not display it? Let's check to see if we've got an error here using a function called iferror.

So, if this function causes an error and division by zero qualifies as one, when that's true, what is it that we want to display? And we could display if we wanted to double quote NA. Can certainly do that if we wanted to or maybe display nothing, just double quote, double quote. Now, when there is no error, we will get an appropriate answer but when there is an error as in this case, as I press enter here, we display nothing.

Again, you'll be the judge as to what you wanna use here. And to make this work in the other cells, we gonna drag this into one cell to the right and then leftward. And so, at different times, if those numbers change or we're making adjustments and we want these to work, so I'll change this back to a 220, everything will look the way it did before but if, for example, here this is a 180, let's see what's happening there. It displays a blank. So, using the iferror function, in an earlier example, the aggregate function, we see how we can work around those division by zero errors to either display information differently or simply to work around that issue.