MARISSA JONES ONLY!!!!

Type a Function

Learning Outcomes

· Select a function by typing

· Use AutoComplete to copy formulas

In addition to using the Insert Function dialog box, the AutoSum button, or the AutoSum list arrow on the Ribbon to enter a function, you can manually type the function into a cell and then complete the arguments needed. This method requires that you know the name and initial characters of the function, but it can be faster than opening several dialog boxes. Experienced Excel users often prefer this method, but it is only an alternative, not better or more correct than any other method. The Excel  Formula AutoComplete  feature makes it easier to enter function names by typing, because it suggests functions depending on the first letters you type.  You want to calculate the maximum and minimum quarterly expenses in your worksheet, and you decide to manually enter these statistical functions.

Steps

1. 1

Click cell B16, type =, then type m

Because you are manually typing this function, it is necessary to begin with the equal sign ( = ). The Formula AutoComplete feature displays a list of function names beginning with “M” beneath cell B16. Once you type an equal sign in a cell, each letter you type acts as a trigger to activate the Formula AutoComplete feature. This feature minimizes the amount of typing you need to do to enter a function and reduces typing and syntax errors.

2. 2

Click MAX in the list

Clicking any function in the Formula AutoComplete list opens a ScreenTip next to the list that describes the function.

Quick Tip

When you select the function, a ScreenTip automatically displays more detailed information about the function.

3. 3

Double-click MAX

The function is inserted in the cell, and a ScreenTip appears beneath the cell to help you complete the formula. See  Figure 2-6 .

Figure 2-6MAX Function in Progress

4. 4

Select the range B4:B11, as shown in  Figure 2-7  , then click the Enter button  on the formula bar

Figure 2-7Completing the MAX Function

The result, 7295.06, appears in cell B16. When you completed the entry, the closing parenthesis was automatically added to the formula.

5. 5

Click cell B17, type =, type m, then double-click MIN in the list of function names

The MIN function appears in the cell.

6. 6

Select the range B4:B11, then press [Enter]

The result, 1568.25, appears in cell B17.

7. 7

Select the range B16:B17, then drag the fill handle from cell B17 to cell E17

The maximum and minimum values for all of the quarters appear in the selected range, as shown in  Figure 2-8 .

Figure 2-8Completed MAX and MIN Functions

8. 8

Save your work

Using the COUNT and COUNTA Functions

When you select a range, a count of cells in the range that are not blank appears in the status bar. You can use this information to determine things such as how many team members entered project hours in a worksheet. For example, if you select the range A1:A5 and only cells A1, A4, and A5 contain data, the status bar displays “Count: 3.” To count nonblank cells more precisely, or to incorporate these calculations in a worksheet, you can use the COUNT and COUNTA functions. The COUNT function returns the number of cells in a range that contain numeric data, including numbers, dates, and formulas. The COUNTA function returns the number of cells in a range that contain any data at all, including numeric data, labels, and even a blank space. For example, the formula =COUNT(A1:A5) returns the number of cells in the range that contain numeric data, and the formula =COUNTA(A1:A5) returns the number of cells in the range that are not empty. If you use the COUNT functions in the Quick Analysis tool, the calculation is entered in the cell immediately beneath the selected range.