Statistical Functions in Microsoft Excel
As you read, review Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to a large set of data entered in a spreadsheet. The rest of this section details how to use each type of function in a budget spreadsheet.
The Count Function
Data file: Continue with CH2 Personal Budget.
The next function that we will add to the Budget Detail worksheet is the COUNT function. The COUNT function is used to determine how many cells in a range contain a numeric entry. The COUNT function will not work for counting text or other non-numeric entries. For the Budget Detail worksheet, we will use the COUNT function to count the number of items that are planned in the Annual Spend column (Column D). The following explains how the COUNT function is added to the worksheet by using the function list:
- Click cell D13 in the Budget Detail worksheet
- Type an equal sign: =
- Type the letter C
- Click the down arrow on the scroll bar of the function list (see Figure 2.17) and find the word COUNT
- Double click the word COUNT from the function list
- Highlight the range D3:D11
- You can type a closing parenthesis ) and then press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The function produces an output of 9 since there are 9 items planned on the worksheet.
Figure 7 shows the function list box that appears after completing steps 2 and 3 for the COUNT function. The function list provides an alternative method for adding a function to a worksheet.
Figure 7 Using the Function List to Add the COUNT Function
Figure 8 shows the output of the COUNT function after pressing the ENTER key. The function counts the number of cells in the range D3:D11 that contain a numeric value. The result of 9 indicates that there are 9 categories planned
for this budget.
Figure 8 Completed COUNT Function in the Budget Detail Worksheet