Using Excel error-checking functions

- On this worksheet we're seeing a list of some Excel error-checking functions, ISERR, ISERROR, IFERROR, the AGGREGATE function by the way does a lot of other things as well. I'm going to be testing this with the information we see over in Column C here. Make that a bit wider, be using formulas. Imagine you've got a list of social security numbers somewhere and another list somewhere else. In this case they're right next to each other, but it could be from different worksheets. We want to see if this number exists in the list over in Column A.

So one approach is to use the Match function. Match function says I've got some data somewhere, in this case it's C2, comma, we're looking in Column A to see if it's there, comma, and we're not looking for numbers that are close but high or close but low, it's got to be an exact match. As you would imagine, this would be most of the time with social security numbers. And as I press Enter, it did find the information, it's in Row 7, you can see it over there. Works just fine. But what if it doesn't find the information? I'm going to change this number, change the last character there, and now we get N/A.

In some situations that could be just fine, it's a clear indication it wasn't found. On the other hand, you might want to actually display this with an actual text entry. So let's begin by using one of the other functions, ISERROR, and putting this inside of an IF function. Let's check to see if this is an error and then display something else other than N/A. ISERROR, so in English we're about to say, "if this is an error," what do we want to say? We might want to display nothing. I think most of the time that isn't the best choice.

Here we'd put in double quote if we want to display blank. But if we want text, between the two double quotes, put in the phrase we want, text we want, "Not found." Now, when there is no error, remember, this is what happens when we do get an error there, when there is no error, comma, let's actually display the function that we hoped to work all along, in other words, let's take this data right here, highlight it, press "Ctrl + c," click behind the comma, "Ctrl + v" to paste it. And a final right parenthesis, and there we go, "Not found." So it's either "Not found" or it comes up with the appropriate answer.

If that number is correct, it just comes up with the answer. So that's one approach. I'm going to display this with a new function available in Excel 2013 called FORMULATEXT. There it is right there, we can just Tab it into place. This allows you to display the actual content of a formula located elsewhere. So that's what that looks like there. Now, another approach could be this function here, relatively new in Excel, called IFERROR. The IFERROR function says, "if this is an error." Now I'm going to actually, so I'll simply type in "match," as we saw it before, C2 comma A comma zero.

The IFERROR function says, "when this is an error," comma, let's put in that phrase "Not found." But, much simpler than our previous example, that's it. If this is not an error, let's simply do it. And I think that's what we were thinking about in the previous one, but this one's much shorter. As I press Enter, we'll get the 7, or if this is incorrect, we get the "Not found." Let me display this, too. I can simply copy this formula text used up here, copy it down here, drag it with the Ctrl key, put it right here. So there's how the two functions look side by side.

And with the current value of the social security number being as we see it here, we see the difference. And so many, many times the IFERROR function is going to be better. It's shorter, easier to read, and easy to figure out. But again, think of the other situation. Now, ISERR, and you see the description off to the right there, and I'll bring the box over and down a little bit so we can see it, it does exactly what ISERROR does, except it doesn't recognize N/A as an actual error. And so in our example up here, if we were to use that, it might sound a little bit strange here, but if we were to use ISERR, it will not recognize that as an error.

In other words, it'll say in effect, "I don't see an error here," because it doesn't check for the N/A result. And so it actually, what's it going to do here? It's going to try and perform the function, and then actually present it with N/A. It might sound a little bit like double talk as we bring this up, but again, recognize the difference in ISERR and ISERROR. Very similar, and many times they're interchangeable, but not if N/A is involved as a possible answer. Now, the AGGREGATE function works a bit differently. So I'll move this box off to the side again, and look at the data over in Columns E and F.

Make Column C narrower for the moment, we don't need to be focused on that. Move this over a bit. We've got a list of data in Column E and F, some dates, some adjusted sales. For whatever reason we've got some errors here. Now the list could be enormous. In this case it's not that huge, but there's a division by zero problem, down here there's a name problem. Some of those problems, by the way, are referred to in this list over here. Bring it over a bit more and we see some of the various Excel formula errors that pop up at different times. We can keep that on the screen for the moment. But let's focus on a formula that we have in Cell H3.

This is trying to add up the data in Column F, and it doesn't work because there's an error there. Now, that's the SUM function, here's the AVERAGE function, that doesn't work either, we've got problems. Here's the MIN function, that's not working, and below that, let me scroll up a little bit and move this, we also have the MAX function. Now, all these will work if we use a function called AGGREGATE. By the way, if you are on the Formulas tab and you happen to discover this function, it's under Math & Trig, when you see AGGREGATE, the description really doesn't help very much.

"Returns an aggregate in a list or database." Exactly helpful. However, let's use this function. As with longer name functions, when you start to type a function, "=a," you see all the A functions, as soon as we see it in the list we can just click and Tab it into place if we wish. The AGGREGATE function has three arguments to it, and the first one goes on and on and on, it gives us 19 choices. What are we trying to do here? I would think many, many times as you use this, it's likely to be nine or possibly one. Nine is sum, one is average. If you are familiar with the SUBTOTAL function, you'll recognize the number and the order and the choices here as being very similar.

So we'll just use nine here, we could Tab it in or just type 9, comma, but here are the choices that are going to give us the actual solution that we need here. Notice how they're numbered zero through seven, so there are eight choices. And you can imagine using this in different ways. If you are familiar with the SUBTOTAL function, you'll recognize some advantages to using AGGREGATE, and notice other situations here, something about hidden rows, sometimes that comes into play, but the focus here is on the error values. We want to ignore the error values. So we put in the number 6, comma, and now finally the location of what we're trying to add up.

It's in Column F, I'll simply click Column F, and we do have an answer now. It has ignored the error problems. Now we could verify this by highlighting the data here, and with the Ctrl key, highlighting this data, letting go of that, and with the Ctrl key highlighting this, and you eventually get down to the bottom of the list, and this, and this, in other words, we're highlighting all the non-errors, and on the status bar, bottom of the screen, we'll see the sum, 17382. It displays a rounded version, there's our actual answer.

So you see how it works. This same idea would work here. Now if I bring this downward, it's not going to be right just yet, because it's still doing a sum, but if we want that to be an average, the option here, 1, we see it, and we're going to get a sensible answer that way too. 263. So the AGGREGATE function does give us this possibility of avoiding errors and working around them. Now, the next worksheet over, and backtracking a little bit, we've got a situation here that looks rather innocuous and nothing too unusual going on. There are formulas here that are calculating the percent of profit change.

And everything is all well and good, but what would happen if, on one of these months, the expenses equal the sales? I'm going to type 275 here. That will cause the profits to become zero, and we see this happening. Now, in one sense, that's okay, it does point out what happens here. We're trying to divide by zero. You can't divide by cell D4, it's got zero in it. Can't do that. So we get this. So the question would be, how can we work around that? Possibly we could use IFERROR, so we could type =IFERROR, if that's an error, comma, let's just display nothing, or possibly we could display "N/A." You can display this as text, but you can actually display it as a function.

This is one option. Remember, when there is no error, perform the calculation. So I could put that there, that's what we see, if I drag it to the right, we should still see those percentages, because no error is occurring. And we would also drag that to the left for all these. So any time this is zero, this formula's going to work okay. So if I make this 198 here, again we'll have a zero profit, we see this again. But there's another issue here, too, that we'd have to work around using perhaps IFERROR or possibly ISERROR and so on.

What if one of these numbers is negative? For example, if the February expenses are 220, that's negative. And what if the March expenses over here are 240? We've got a number of problems here. First of all, if the profits are at 37.50 and they drop to -22, the 159% is a meaningless number, and even worse, if the profits are -22 and we jump to positive 35, this calculation here says you've grown by, what, minus 259%? That doesn't make any sense at all.

So we might want to reconsider all these formulas, maybe starting right here, and say the following: if any of these numbers here happen to be zero or below zero, let's not perform the calculation at all. So, we could just start all over by saying, if this number here is less than or equal to zero, comma, or this number here is less than or equal to zero, if either of those is true, now we can put the word "or" in front of them, this way. So we don't necessarily need the other error checking capability, but we're doing this, you might say in a manual way.

If either of those situations is true, let's just put in "N/A," or maybe nothing. You can do it that way. But otherwise, let's do what we were doing all along before, and that was taking this cell, divide it by this cell, minus one. So we see what's happening there. And the reason this is N/A is because this was negative. Remember, our calculation always refers to these two cells. So we see different approaches here to error handling routines. And remember, these functions are the ones you're likely to be using in different ways, and we saw some examples of some of these earlier.