PRDV006: Spreadsheets II: Formatting and Functions

The AVERAGEIF function performs the same mathematical calculation as the AVERAGE function. However, like the COUNTIF function, it allows you to define criteria to choose cells in a range used in the function output. The AVERAGEIF function differs from the COUNTIF function in that it allows you to define two cell ranges instead of one. The first range pertains to the criteria that will be used to select cells for the function output. The second range contains the values that will be used to calculate the arithmetic mean.

Table 1 defines the arguments in the AVERAGEIF and SUMIF functions.

We will use the AVERAGEIF function in the Portfolio Summary worksheet to calculate the average length of time for each investment type held.

These steps explain how to add this function to the worksheet:

1. Click cell C4 on the Portfolio Summary worksheet.
   
   
2. Click the Formulas tab on the Ribbon.
   
   
3. Click the More Functions button in the Function Library group of commands.
   
   
4. Place the mouse pointer over the Statistical option from the drop-down list (see Figure 1).
   
   
5. Click the AVERAGEIF function, which will be near the top of the list of functions. This opens the Function Arguments dialog box.
   
   
6. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 4).
   
   
7. Click the Investment Detail worksheet tab.
   
   
8. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
   
   
9. Click in the Range argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
   
   
10. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.
    
    
11. Click the Collapse Dialog button next to the Average_range argument on the Function Arguments dialog box (see Figure 4).
    
    
12. Click the Investment Detail worksheet tab.
    
    
13. Highlight the range Q4:Q18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
    
    
14. Click in the Average_range argument input box and place an absolute reference on the range Q4:Q18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
    
    
15. Click the OK button at the bottom of the Function Arguments dialog box.


Figure 4 shows the Function Arguments dialog box for the AVERAGEIF function that will be input into cell C4. Notice that absolute references are placed on the cell locations that define the Range and Average_range arguments. The function will evaluate the cells in the range A4:A18 using the value in cell A4 on the Portfolio Summary worksheet. When a cell in the range A4:A18 meets the criteria, the function will pull the cell location in the same row from the range Q4:Q18 and include it in the average calculation.






Figure 4 Defined Arguments for the AVERAGEIF Function



16. Copy the function in cell C4 and paste it into the range C5:C7 using the Paste Formulas option.
    
    
17. Type an equal sign (=) in cell C8.
    
    
18. Click the Investment Detail worksheet tab. Then click cell Q19 and press the ENTER key on your keyboard.
    

Figure 5 shows the output of the AVERAGEIF function in the Average Months Owned column on the Portfolio Summary worksheet. The function calculates the average months owned in Column Q on the Investment Detail worksheet, where the investment type is equal to the description entered in the range A4:A7 on the Portfolio Summary worksheet.

Figure 5 AVERAGEIF Function Output on the Portfolio Summary Worksheet