Explanation

In this example, the goal is to calculate an average for each group and region in the data as shown in the worksheet. For convenience, data is an Excel Table in the range B5:D16. This problem can be easily solved with the AVERAGEIFS function . Like the COUNTIFS function and SUMIFS function , the AVERAGEIFS function is designed to accept multiple criteria entered in [range, criteria] pairs. As long as this information is supplied correctly, the behavior of AVERAGEIFS is fully automatic.

AVERAGEIFS function

The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria . The generic syntax for AVERAGEIFS looks like this:

=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2,...)

In this problem, we need to configure AVERAGEIFS to average sales with two criteria: (1) group and (2) region. We start off with the average range , which contains the values to average in column D:

=AVERAGEIFS(data[Sales],

Next, we need to enter the criteria needed to target the group. The criteria range is data[Group] . For the criteria, since we already have group names in column F, we will pick up those values directly with a reference to F5:

=AVERAGEIFS(data[Sales],data[Group],F5,

If we entered this formula as-is, it would calculate an average for group “A”, ignoring regions. Next, we need to enter the criteria needed to target the regions. In this case, the criteria range is data[Region] and the criteria itself comes from cell G5:

=AVERAGEIFS(data[Sales],data[Group],F5,data[Region],G5)

This is the final formula entered in cell H5. As the formula is copied down, it calculates an average for each group and region in the summary table using the values in columns F and G for criteria.

Explanation

In this example, the goal is to calculate a quiz score average for each person listed in column D using the four scores in columns C, D, E, and F. The standard way to solve this problem in Excel is to use the AVERAGE function .

AVERAGE function

The AVERAGE function calculates the average (arithmetic mean) of numbers provided as arguments . In this example where there are only four quiz values to work with, you could use AVERAGE with separate cell references like this:

=AVERAGE(C5,D5,E5,F5)

This is a perfectly valid formula, but it will become tedious to enter if there are many values. The more typical way to solve this problem (where all values to average are in a continuous range) is to provide just one range to AVERAGE as seen in cell H5 of the worksheet shown:

=AVERAGE(C5:F5)

To calculate an average, AVERAGE sums all numeric values and divides by the count of numeric values. The count used by AVERAGE depends on the data. Note that text values and empty cells are ignored, as you can see in rows 9 and 11 of the worksheet shown. However, zero (0) values are included as you can see in row 13.

Notes

  • AVERAGE includes zero values in the calculation. If you need to ignore zero (0) values in a set of data, you can use the AVERAGEIFS function .
  • If the values given to AVERAGE contain errors, AVERAGE will return an error. You can use the AGGREGATE function to average and ignore errors .