AVERAGEIF Function Overview
One handy use for the function is to ignore zero values in your data that can throw off the average.
AVERAGEIF Function Syntax and Augments
The syntax for the AVERAGEIF function is:
=AVERAGEIF( Range, Criteria, Average_range)
The arguments for the AVERAGEIF function are:
Range - (required) the group of cells the function is to search
Criteria - (required) determines whether the data in a cell is to be averaged or not
Average_range - (optional) 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.
In this example, we will omit the Average_range argument since the range we want to average is the same one that we will search for zero values.
Example: Ignore Zeros when Finding the Average
In this example we will find the average value for data in the range D1 to D7 that do not equal zero.
To do this we will set the criteria argument to ignore all zero values in the range.
- Enter the following data into cells D1 to D7: 10, 20, 0, 30, 0, 40
- Click on cell D8 - the location where the function results will be displayed
- Click on the Formulas tab of the ribbon
- Choose More Functions > Statistical from the ribbon to open the function drop down list
- Click on AVERAGEIF in the list to bring up the function's dialog box
- In the dialog box, click on the Range line
- Drag select cells D1 to D7 in the worksheet to enter this range into the dialog box
- On the Criteria line in the dialog box, type 0
- Note: the "" character is the does not equal symbol in Excel and it is created by typing the angle brackets - located in the bottom right corner of the keyboard - back to back
- Click OK to close the dialog box and complete the function
- The answer 25 should appear in cell D8
- Since the function ignores the zeros in cells D3, D5, and D7, the average of the remaining four cells is 25 : (10+20+30+40) /4 = 25
- If you click on cell D8 the complete function = AVERAGEIF( D1:D7, "0" ) appears in the formula bar above the worksheet