AVERAGEIF function

The AVERAGEIF function returns the average of cells in a range that meet criteria you provide.

Let’s determine the average for sales that are greater than $60000.

Type an = sign, AVERAGEIF, opening parenthesis (in this example, we are going to evaluate and average the same range of cells, C2 through C5, in the Sales column), comma, then we type the criteria against which the range is evaluated, enclosed in quotes (you put it in quotes so Excel interprets the operator and value correctly), in this example, greater than 60000.

Don’t type a comma in 60000. Excel would interpret that as a separator for arguments of the function and return an error.

And press Enter. Excel automatically adds the closing parenthesis to the formula.

The average for sales greater than $60000 is $71229.

Let’s walk through this.

The function evaluates how many cells in the Sales column meet the criteria of greater than 60000. There are three cells.

The function then averages these cells.

Now we’ll use the AVERAGEIF function to determine the average for sales where the number of orders is greater than 50.

In this example, one range of cells is evaluated against the criteria and a second range of cells is averaged.

We type = AVERAGEIF, opening parenthesis, the range of cells we want evaluated (cells B2 through B5) in the number of orders column, comma, the criteria by which the range is to be evaluated enclosed in quotes (greater than 50), comma, the range of cells we want to average (cells C2 through C5 in the Sales column), and press Enter.

The average for sales where the number of orders is greater than 50 is $66487.

Let’s walk through this one, too.

First, the function evaluates which cells in B2 through B5 meet the criteria of greater than 50.

Three cells meet the criteria. The function then averages the cells from the Sales column where the criteria is met by the corresponding cell in column B.

For more information about the AVERAGEIF function, see the course summary at the end of the course.

Up next, Weighted average.