Recognizing Excel Formula errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM, and #NAME!

- [Instructor] When you work with formulas in Excel you're bound to encounter errors at different times. Many times the errors are obvious. Maybe you referred to a wrong cell or you mistype a formula name, that sort of thing. In this worksheet, in the lower left corner in the green box, we see the different error types listed. Notice how they all begin with the same symbol. That symbol found on the number three key on most keyboards, the hashtag, pound sign, number sign, whatever you want to call it. And they exist in so many different situations. Many times, as I suggested, you don't care what they are but there's a reason for each one of them.

We see the reasons there. Let's show you some examples of how these happen. I'm working with some data over in column L. I'm trying to find out which number appears most often. I don't know if there is one or anything, but there is a function called =mode. It will count the number that appears most often within a list of numbers. So I'm looking in column L, press Enter, and I get that #N/A, not applicable. Meaning what? Well, if you did a little bit of research on the =mode function you'd see that's the kind of answer you're going to get if no entry here appears more than any others.

There's no one entry that's most common. Now, if I make a quick change here, I'll copy the 773 in to the next cell, now that it's there twice now, and we do get that answer. It appears there more often than any other entry. So that's one simple example of how we could have seen, I'll press CTRL+Z, again that #N/A or inapplicable kind of error. Another example could be something like this. Maybe we're looking for the number 777. We could be using the =match function and that 777 could be an adjacent cell, but sometimes we'll just type it in right here.

We're looking for 777. Does that exist over in column L? And if we put in a zero here, that's for an exact match, let's see where it's found. Well it wasn't found at all. So #N/A again, indicates that a value is not available to a formula. If we were looking for 773 we would find that. And that's found in the third position, because our reference here was to the entire column. Different times we'll see that #N/A. Not available to a formula. Now sometimes we'll see value.

I think this is probably more common than most of the other error types. Here's a simple little example. It turns out that in column D these profit entries here are not formulas, perhaps they should be, because some of these numbers might need to be changed. We want formulas here instead. So I'm going to type a formula in here and then copy it down into column D. So I'm in cell D2 and I'm about to type, and maybe I'm just slightly less attentive here, and I type in =b1-c1. I get those pound signs/hashtags.

I'm going to make column D a bit wider, and I would have seen #VALUE!. I saw one over here and here and here. So of course what did I do? I typed reference to these two cells. You can't subtract those. So I'll simply come back and type in what I should have typed in, =b2-c2. There we go. And I will copy that down the column so that these are all formulas. So we got rid of the #/VALUE! error. Another example could be over here. I'm using the =find function. Maybe I haven't used this in a long time and actually I'm misusing it, or I'm about to misuse it, I'm looking for 777, comma, over in column L.

And as I press enter it's not found. At least that's what I'm thinking right now. Once again #/VALUE! there. What if I'm looking for 773? It is found over there. But what happens? Actually this function is designed to find data inside of a cell, so it's still not working. So I better do my homework here and figure out how =find really works. And we wouldn't really be using it in this case. The =match function is the one we should've used, which you saw in an earlier example. So any number of different situation that might cause that #/VALUE!.

Here's another one. A function called =ROMAN, probably not used a whole lot. It allows us to convert numbers in to Roman numerals. We want these other numbers to be used as well, so I'll double-click to copy this down in to the next few cells. And we see what's happening here. #/VALUE!. Now why is that happening? You wouldn't necessarily know, but the =ROMAN function only works until the number 3999. Doesn't work at 4000, that's the boundary. That doesn't work. But 3999 does. So one more example of how we might see that #/VALUE! error appear.

The =ref function, most often occurs when you're deleting data and there's formulas that refer to it. I'm about to insert a new column to the left of column C. I'll right-click column C. And for whatever reason, not paying attention, I choose Delete instead. And what happens? #REF! everywhere. Undo that with CTRL+Z. Sometimes it will happen when you delete a cell. Maybe I meant to erase the cell, the number's not right, somehow or other I right-click and Delete and shift the cells up.

And once again we see that same kind of error. And again I'll undo this with CTRL+Z. A number of different examples there, most often associated with deleting cells that are referenced in other formulas. Division by zero. I've got some formulas here, and they are accurate. It's calculating Profit Change, month by month, here. But I never anticipated this idea. What if the Sales and Expenses for a given month happen to be identical? That means the Profits will be zero. And these formulas, for example this one right here, is dividing, or is about to divide, by D3.

But what if D3 is zero? Or up here, D2 is zero? I'll make these two entries right here for the moment, the Sales and Expenses, be identical. And of course that could happen in a real-life situation. And I've got this kind of an error. Typically we don't want to see these on a worksheet. Now what do we do here? We probably would want to display something else. So one kind of function that you might use in this situation, and I'll zoom in on it to make it larger, is called IFERROR So we could type =IFERROR, and in English we're about to say the following, "If this causes an error, comma let's just display nothing double quote, double quote.

Or anything we want between the double quotes if we wish. If we want nothing to happen here, then we simply need a right parentheses and also another one here to the left. You can see by the color coding that it alerts us to this issue. So what are we saying in English? If this causes an error, display blank. If it does not cause an error, then perform the function. So I'll do that. And we do want to copy this down in to the next two cells, I'll double click here, and also copy it upward, so the other answers are working just fine.

Now again it's up to you to decide what it is you want to have happen here. I put in double quote, double quote to display a blank. You could put in a text or a word or say not applicable, or you could type in zero or something. Anyway we override that #DIV/0! Division by zero, you cannot do that. This sometimes happens too when you're dealing with averages that might be zero, and you're trying to divide by that. Division by zero, a not uncommon error that we encounter at different times, as we saw in the example here.

The NUM entry. Lots or examples how we might use this. Here's one off to the right. Maybe this one's a little bit obscure, but you can convert data from decimal into binary, you can go from binary into decimal. And also there's a whole set of functions relating to decimal, octo- and hexadecimal and so on. That might be a bit obscure for a lot of users, but if I was trying to use that function right here, in other words I want to see that number displayed as a binary number, this case the function is called =dec2bin, with the number two taking the place of the letters t-o-, =dec2bin meaning decimal to binary.

Left parentheses. And I use it on this cell right here. And no big surprise, if I'm familiar with that kind of a concept. Now I'll double-click and copy this down the column. Everything is looking all well and good. This is similar to what we did with the Roman Numerals. But this time we get an #NUM! answer. And you wouldn't necessarily know what's going on here, but the maximum size we can use here is 511. 512 will not work. You'll see what happens here. That's the boundary. But 511 will. There's certainly other examples about how you might encounter that as well, the #NUM! error that we see.

The last entry here is called #NULL, and that's unusual. Most people will not encounter this at all; an intersection of two ranges. In this worksheet the range name Expenses refers to these cells, and the name Feb refers to these two cells. So you can write a so-called intersection formula, I'll write it right here. And we can write these in no particular order. =Feb expenses, don't really have to capitalize it, Expenses. Enter.

We get an answer. And I could do that for the other month's and I could also do it for Sales. And that's handy at times. But what if I type in, and I wouldn't on purpose of course, I would types in =Sales Expenses, these ranges do not intersect. And so we get that #NULL! Value. So different examples in here about how we might encounter these functions. Again, without necessarily obsessing over the exact definition of each, any time you see any of these you know you got a problem with the formula. And many times you can figure it out without referring to this list. But it is handy to know and occasionally understand why the error occurs and what it means.