Excel displays a message when you attempt to enter a formula that is not constructed correctly.
✔ Excel returns an error message in the cell when there is something wrong with the result of the calculation.
First, look at what happened when I tried to finish entering a formula that had the wrong number of parentheses. Figure 1-19 shows this.
Figure 1-19: Getting a message from Excel.
Excel finds an uneven number of open and closed parentheses. Therefore, the formula cannot work (it does not make sense mathematically), and Excel tells you so. Watch for these messages; they often offer solutions.
On the other side of the fence are errors in returned values. If you got this far, the formula’s syntax passed muster, but something went awry nonetheless. Possible errors include
✔ Attempting to perform a mathematical operation on text
✔ Attempting to divide a number by 0 (a mathematical no-no)
✔ Trying to reference a nonexistent cell, range, worksheet, or workbook
✔ Entering the wrong type of information into an argument function
This is by no means an exhaustive list of possible error conditions, but you get the idea. So what does Excel do about it? There are a handful of errors that Excel places into the cell with the problem formula.
Chapter 4 discusses catching and handling formula errors in detail.
Using Functions in Formulas
Functions are like little utility programs that do a single thing. For example, the SUM function sums numbers, the COUNT function counts, and the AVERAGE function calculates an average.
There are functions to handle many needs: working with numbers, working with text, working with dates and times, working with finance, and so on. Functions can be combined and nested (one goes inside another). Functions return a value, and this value can be combined with the results of another function or formula. The possibilities are nearly endless.
But functions do not exist on their own. They are always a part of a formula. Now, that can mean that the formula is made up completely of the function or that the formula combines the function with other functions, data, operators, or references. But functions must follow the formula golden rule: Start with the equal sign. Look at some examples:
Ready to write your first formula with a function in it? Use the following steps to write a function that creates an average:
1. Enter some numbers in a column’s cells.
2. Click an empty cell where you want to see the result.
3. Type =AVERAGE( to start the function.
Note: Excel presents a list of functions that have the same spelling as the function name you type. The more letters you type, the shorter the list becomes. The advantage is, for example, typing the letter A, using ↓ to select the AVERAGE function and then pressing the Tab key.
4. Click the first cell with an entered value and, while holding the mouse button, drag the mouse pointer over the other cells that have values.
An alternative is to enter the range of those cells.
5. Type).
6. Press Enter.
If all went well, your worksheet should look a little bit like mine, in Figure 1-20. Cell B10 has the calculated result, but look up at the Formula Bar, and you can see the actual function as it was entered.
Figure 1-20: Entering the AVERAGE function.
Formulas and functions are dependent on the cells and ranges to which they refer. If you change the data in one of the cells, the result returned by the function updates. You can try this now. In the example you just did with making an average, click one of the cells with the values and enter a different number. The returned average changes.
Most functions take inputs – called arguments or parameters – that specify the data the function is to use. Some functions take no arguments, some take one, and others take many; it all depends on the function. The argument list is always enclosed in parentheses following the function name. If there’s more than one argument, the arguments are separated by commas. Look at a few examples:
Some functions have required arguments and optional arguments. You must provide the required ones. The optional ones are, well, optional. But you may want to include them if their presence helps the function return the value you need.
The IPMT function is a good example. Four arguments are required, and two more are optional. You can read more about the IPMT function in Chapter 5. You can read more about function arguments in Chapter 2.
Memorizing the arguments that every function takes would be a daunting task. I can only think that if you could pull that off, you could be on television. But back to reality. You don’t have to memorize arguments because Excel helps you select what function to use and then tells you which arguments are needed.
Figure 1-21 shows the Insert Function dialog box. You access this great helper by clicking the Insert Function button on the Formulas Ribbon. The dialog box is where you select a function to use.
Figure 1-21: Using the Insert Function dialog box.
The dialog box contains a listing of all available functions – and there are a lot of them! So to make matters easier, the dialog box gives you a way to search for a function by a keyword, or you can filter the list of functions by category.
Try it! Here’s an example of how to use the Insert Function dialog box to multiply a few numbers:
1. Enter three numbers in three different cells.
2. Click an
