Excel Error Messages Part 2: When is an error not an error?
There are three types of messages Excel gives us that aren’t technically error messages, but they are notifying us that something MIGHT be wrong.
In this second half of my Excel Error Messages training, we’ll explore #N/A, FALSE and circular cell references.
#N/A
#N/A actually means no answer. The most common place you’ll see an #N/A is when you use a VLookUp function. For those unfamiliar with the term VLookUp, it’s a function that allows you to look up a selected text string in a list and return some information about it. For example, you can look up a customer number and be returned a customer name. There is an optional argument in VLookUp that will respond #N/A if it could not find the string.
One of the most common questions is how to get Excel to tell you something other than #N/A. Meet the Excel function: ISNA. ISNA is a question that asks, “Will this formula resolve to no answer or N/A?” So, instead of returning #N/A if an old product number doesn’t show up in a new product list, it can show TRUE or FALSE under the Discontinued column.
FALSE
Sometimes we just see that FALSE message show up in a cell and we don’t know why. While it can be a legitimate cell value, as in the above ISNA example, it can sometimes show up when we make a mistake typing in a formula. If, for example, we try to type in =A1>B1 and it is not the case, then Excel tells us FALSE. In this case I would wonder if the formula writer meant to type in A1/B1. If you look at your keyboard, those two keys are pretty close.
Circular Cell References
One of the most aggravating messages to get is the circular cell reference warning. A circular cell reference warning occurs when some part of the formula is referring back to the cell that the formula is being typed into. So, for example, in this case, we are trying to add C1 to A1 and B1, but we’re typing the formula into C1.
It would be awesome if all circular cell references were this easy to resolve. If it’s not obvious to you, click OK and Excel will attempt to show you where the problem is.
Just in case you are curious, there are applications for a circular cell reference, which is why it gives you the option. Most of the time, however, it is an error.
In this second half of my Excel Error Messages training, we’ll explore #N/A, FALSE and circular cell references.
#N/A
#N/A actually means no answer. The most common place you’ll see an #N/A is when you use a VLookUp function. For those unfamiliar with the term VLookUp, it’s a function that allows you to look up a selected text string in a list and return some information about it. For example, you can look up a customer number and be returned a customer name. There is an optional argument in VLookUp that will respond #N/A if it could not find the string.
One of the most common questions is how to get Excel to tell you something other than #N/A. Meet the Excel function: ISNA. ISNA is a question that asks, “Will this formula resolve to no answer or N/A?” So, instead of returning #N/A if an old product number doesn’t show up in a new product list, it can show TRUE or FALSE under the Discontinued column.
FALSE
Sometimes we just see that FALSE message show up in a cell and we don’t know why. While it can be a legitimate cell value, as in the above ISNA example, it can sometimes show up when we make a mistake typing in a formula. If, for example, we try to type in =A1>B1 and it is not the case, then Excel tells us FALSE. In this case I would wonder if the formula writer meant to type in A1/B1. If you look at your keyboard, those two keys are pretty close.
Circular Cell References
One of the most aggravating messages to get is the circular cell reference warning. A circular cell reference warning occurs when some part of the formula is referring back to the cell that the formula is being typed into. So, for example, in this case, we are trying to add C1 to A1 and B1, but we’re typing the formula into C1.
It would be awesome if all circular cell references were this easy to resolve. If it’s not obvious to you, click OK and Excel will attempt to show you where the problem is.
Just in case you are curious, there are applications for a circular cell reference, which is why it gives you the option. Most of the time, however, it is an error.
