Читать книгу Excel Formulas and Functions For Dummies - Bluttman Ken - Страница 7

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.

A formula can consist of nothing but a single function – preceded by an equal sign, of course!

Looking at what goes into a function

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.

Arguing with a function

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.

If you know which category a function belongs in, you can click the function category button on the Formulas Ribbon and select the function from the menu.

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 empty cell where you want the result to appear.

3. Click the Insert Function button on the Formulas Ribbon.

As an alternative, you can just click the little fx button on the Formula Bar. The Insert Function dialog box appears.

4. From the category drop-down list, select either All or Math & Trig.

5. In the list of functions, find and select the PRODUCT function.

6. Click the OK button.

This closes the Insert Function dialog box and displays the Function Arguments dialog box (see Figure 1-22), where you can enter as many arguments as needed. Initially, the dialog box may not look like it can accommodate enough arguments. You need to enter three in this example, but it looks like there is only room for two. This is like musical chairs!

More argument entry boxes appear as you need them. First, though, how do you enter the argument? There are two ways.

7. Enter the argument in one of two ways:

● Type the numbers or cell references in the boxes.

● Use those funny-looking squares to the right of the entry boxes.

In Figure 1-22, two entry boxes are ready to go. To the left of them are the names Number1 and Number2. To the right of the boxes are the little squares. These squares are actually called RefEdit controls. They make argument entry a snap. All you do is click one, click the cell with the value, and then press Enter.

8. Click the RefEdit control to the right of the Number1 entry box.

The Function Arguments dialog box shrinks to just the size of the entry box.

9. Click the cell with the first number.

Figure 1-23 shows what the screen looks like at this point.

10. Press Enter.

The Function Arguments dialog box reappears with the argument entered in the box. The argument is not the value in the cell, but the address of the cell that contains the value – exactly what you want.

11. Repeat Steps 7–9 to enter the other two cell references.

Figure 1-24 shows what the screen should now look like.

The number of entry boxes and associated RefEdit controls grow to match the number of needed entry boxes.

12. Click OK or press Enter to complete the function.

Figure 1-22: Getting ready to enter some arguments to the function.

Figure 1-23: Using RefEdit to enter arguments.

Figure 1-24: Completing the function entry.

Figure 1-25 shows the result of all this hoopla. The PRODUCT function returns the result of the individual numbers being multiplied together.

Figure 1-25: Math was never this easy!

You do not have to use the Insert Function dialog box to enter functions into cells. It is there for convenience. As you become familiar with certain functions that you use repeatedly, you may find it faster to just type the function directly in the cell.

Nesting functions

Nesting is something a bird does, isn’t it? Well, a bird expert would know the answer to that one; however, I do know how to nest Excel functions. A nested function is tucked inside another function as one of its arguments. Nesting functions let you return results you would have a hard time getting otherwise. (Nested functions are used in examples in various places in the book. The COUNTIF, AVERAGE, and MAX functions are discussed in Chapter 9.)

Figure 1-26 shows the daily closing price for the Standard & Poor’s 500 for the month of September 2004. A possible analysis is to see how many times the closing price was higher than the average for the month. Therefore, you need to calculate the average before you can compare any single price. Embed the AVERAGE function inside another function to calculate the average first.

Figure 1-26: Nesting functions.

When a function is nested inside another, the inner function is calculated first. Then that result is used as an argument for the outer function.

The COUNTIF function counts the number of cells in a range that meet a condition. The condition in this case is that any single value in the range is greater than (>) the average of the range. The formula in cell D7 is =COUNTIF(B5:B25, ">" & AVERAGE(B5:B25)). The AVERAGE function is evaluated first; then the COUNTIF function is evaluated, using the returned value from the nested function as an argument.

Nested functions are best entered directly. The Insert Function dialog box does not make it easy to enter a nested function. Try one. In this example, you use the AVERAGE function to find the average of the largest values from two sets of numbers. The nested function in this example is MAX. You enter the MAX function twice within the AVERAGE function. Follow these steps:

1. Enter a few different numbers in one column.

2. Enter a few different numbers in a different column.

3. Click an empty cell where you want the result to appear.

4. Type =AVERAGE( to start the function entry.

5. Type MAX(.

6. Click the first cell in the second set of numbers, press the mouse button, and drag over all the cells of the first set.

The address of this range enters into the MAX function.

7. Enter a closing parenthesis to end the first MAX function.

8. Enter a comma (,).

9. Once again, type MAX(.

10. Click the first cell in the second set of numbers, press the mouse button, and drag over all the cells of the second set.

The address of this range enters into the MAX function.

11. Enter a closing parenthesis to end the second MAX function.

12. Enter a ).

This ends the AVERAGE function.

13. Press Enter.

Figure 1-27 shows the result of your nested function. Cell C14 has this formula: =AVERAGE(MAX(B4:B10),MAX(D4:D10)).

Figure 1-27: Getting a result from nested functions.

When you use nested functions, the outer function is preceded with an equal sign (=) if it is the beginning of the formula. Any nested functions are not preceded with an equal sign.

You can nest functions up to 64 levels.