Basic average example

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.