AVERAGE

Summary

AVERAGE takes some legwork out of formula creation. It adds up some numeric cells and then divides this by the number of cells it added up. In mathematical jargon, it provides the arithmetic mean of a set of cells.

As with SUM, it only applies to cells that are formatted as numeric values (including dates, percentages etc. as well as your bog-standard numbers). But if you try to do an average across a range that includes some cells containing non-numeric values, it will still work. It will simply ignore those cells in performing its calculation. Similarly, it will ignore logical values (i.e. those that contain a TRUE/FALSE value).

If the cells in your range contain the values 1, 2, 3, Y and 4, then the function will return the value 2.5, calculated as (1+2+3+4)/4. The Y will be ignored both in the sum and the divisor. Blank cells will be similarly ignored. But cells containing a zero will count towards the calculation. So if the cells in your range contain the values 1, 2, 3, 0, 4, the formula will return the value 2, or (1+2+3+0+4)/5.

Syntax

=AVERAGE(Range)

Range can be made up of a single range or a set of distinct ranges. If you're using a set of distinct ranges, they should be separated with commas.

Examples

=AVERAGE(A1:Q20): this will take an average of everything between columns A and Q, in rows 1 through 20

=AVERAGE(A1,C5,D12,D15:D20): this will take an average of the ranges specified

• Tweet