Excel Formulas and Functions For Dummies. Bluttman Ken

Чтение книги онлайн.

      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 СКАЧАТЬ