Excel Formulas and Functions For Dummies. Bluttman Ken

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