As you might have noticed in the previous two pages (Formula Basics and Using Excel's Functions) there are a lot of commonalities between functions and formulas. These commonalities also extend to the possible error messages that may occur.
Cell Referent Errors
You may have noticed this error in one of the cells in the previous example calculating the percent change in the Census 2010 dataset.
#DIV/0! means that you are trying to divide a number by zero which you can't do because the result is undefined. Since we can't do it, Excel can't do it either! So we get this error message. In this example, we can interpret what the error message means. The census tract in question didn't exist in 2000 so we can't measure the percent growth from 2000 to 2010. The original dataset used a dash (-) in cells where a new census tract was created.
Another error message that can sometimes appear is #VALUE!
If we look at the formula bar, we see that someone (gee, I wonder who?) tried to divide information in cell H15 by information in cell H7. They must have made a mistake because what were the expecting to get dividing a number by text? What you get is this error message.
There are other error messages that can come up, but rather than trying to remember them all before they happen, you can learn about them when they appear. Notice that when we click on the cell with the error message that there is a small box with a yellow diamond and an '!' to the left of the cell. When we scroll the mouse over it an arrow appears, as does a text box explaining the error.
Furthermore, if the byline box doesn't clarify what the problem is, you can click on the arrow and get a number of options.
Some Windows versions of Excel have an option called "Show Calculation Steps...," which can make this very easy to resolve. However, some versions on Mac don't include this option. Clicking "Help on this Error" will at least give you some initial guidance.
In this case, it turns out this person is trying to divide a number by text! How ridiculous!!! But also easily resolved.
While the previous errors are derived from the information in the cells referred to in the function or formula, other errors occur because of a problem in the function (or formula) itself.
It happens to the best of us. Sometimes when you are selecting a range of data for a function you misjudge where the dataset ends and quickly press ENTER. Then you see this:
The range of data for calculating the average includes the cell (D8) where the average function is written. OOPS! By clicking OK Excel 2011 gives us this:
But this is easily remedied.
1. Click on the cell (D8) and edit the data range in the formula bar OR
2. Double click on the cell (D8) and edit the data range in the cell OR
3. Delete the function and start over- making sure to stop the range selection at D7 (or typing D2:D7).
I know I've said previously in this tutorial that when using a function in Excel you do not need to type the ending parenthesis- and this is true, try it yourself! But that doesn't mean the Missing Parenthesis error message doesn't pop up from time to time, and I'll show you some common issues that cause it.
If you type a function and include no arguments, but press ENTER you'll get this:
Even though the real problem is that the function contains no arguments, the error message is referring to the missing parenthesis. But since the error message recommends that you check the function, you'll see that there is more of a problem than just a missing parenthesis.
If your formula or function contains an error, and something you typed is inconsistent with Excel's syntax, you'll get the formula error message (even for functions).
In this example the average function was not given a number as an argument:
The error message offers suggestions that include checking out the Function Wizard. All magical entities are important to keep track of, so if you're not sure what it is, check it out here.
REMEMBER: A function, when given the correct arguments, doesn't need the ending parenthesis, but a formula does. A formula is something you write and a function pre-exists in Excel.
You would expect that the Missing Parenthesis error message would pop up if you are writing a formula that includes parentheses (for example, if you are adding or subtacting two numbers before multiplying or dividing them) and forget one end of the pair (either the open '(' or ending ')' parentheses), but check this out:
Instead Excel is helping us out by offering a proposed correction. Sometimes Excel's corrections are exactly what you want, but other times, like in this case, they miss the mark.
CAUTION: Be wary that if you quickly press ENTER when seeing this message it will alter your formula, whereas, if you quickly press ENTER with the previous two error messages, they will give you the opportunity to edit your function.
If you select NO to give yourself the option of editing the formula (which, in this case, you should), you will then get the Missing Parenthesis error message.
Errors happen- it is a part of life and a potential side effect of being human. As long as you pay attention to the error messages that pop up, you will increase the accuracy of your data in Excel.
However, there are errors that occur that Excel doesn't register, especially when manually entering data. Always be vigilant and double check, triple check, QUADRUPLE check your work!!