Excel 2007 AVERAGE IF

The AVERAGE IF function is used to find the average of values in cells in a selected range that meet certain criteria.

The syntax for the AVERAGE IF function is:

=AVERAGEIF( Range, Criteria, Sum Range)

Range - the group of cells the function is to search.

Criteria - determines whether the cell is to be counted or not.

Average_range - the data range that is averaged if the first range meets the specified criteria. If this range is omitted, the first range is averaged instead.

Example Using Excel's AVERAGE IF Function:

Note: For help with this example, see the image to the right.

1. Enter the following data into cells E1 to E6: 114,165,178,143,130,165.
   
   
2. Enter the following data into cells F1 to F6: 10, 20, 30, 10, 20, 30.
   
   
3. Click on cell F7 - the location where the results will be displayed.
   
   
4. Click on the Formulas tab of the ribbon.
   
   
5. Choose More Functions > Statistical from the ribbon to open the function drop down list.
   
   
6. Click on AVERAGEIF in the list to bring up the function's dialog box.
   
   
7. In the dialog box, click on the Range line.
   
   
8. Drag select cells E1 to E6 on the spreadsheet.
   
   
9. On the Criteria line in the dialog box, type "165".
   
   
10. Click on the Average_range line.
    
    
11. Drag select cells F1 to F6 on the spreadsheet.
    
    
12. Click OK.
    
    
13. The answer 25 should appear in cell F7. Since the criteria of equaling 165 is met by only two cells - E2 and E6, only their corresponding cells - F2 and F6 are averaged. The average of 20 and 30 is 25.

• Excel 2007 SUMIF
• Excel 2007 COUNTIF
• Excel IF Function Step by Step Tutorial