AVERAGE practice exercises
Find the mean of a range with AVERAGE, and see how it treats blank cells and text differently from zeros.
How AVERAGE works
AVERAGE finds the mean of your numbers: it adds them up and divides by how many there are. Give it a range, several ranges or single cells, the same way you would with SUM.
=AVERAGE(B2:B6) with scores of 70, 85, 90, 55 and 100 returns 80. If one of those cells is empty, Excel averages the numbers that are left instead of counting the gap as a zero. To tidy up a long result, put AVERAGE inside ROUND: =ROUND(AVERAGE(B2:B7),1) turns a daily visitor average of 124.1666… into 124.2.
AVERAGE skips blank cells and text, but a zero is a real number and pulls the result down. A day with no sales entered and a day with 0 sales give different averages, so decide which one you mean. To leave zeros out, use AVERAGEIF. One extreme value can also drag the mean away from what is typical, and MEDIAN is the better pick then. The exercises below cover a plain average, a range with a blank cell and rounding an average.
- 1Find the average with AVERAGEIn B7, use AVERAGE to find the mean score in B2:B6.
- 2AVERAGE skips blank cellsTuesday has no sales figure yet. In B7, use AVERAGE on B2:B6 to get the average of the days that do have one.
- 3Round an average with ROUNDIn E1, work out the average number of visitors per day, rounded to one decimal. Put AVERAGE inside ROUND: ROUND(AVERAGE(B2:B7),1).
Functions in this topic
Read the guide
Questions
How do I average numbers in Excel and ignore zeros?
Use AVERAGEIF with a "not equal to zero" criterion: =AVERAGEIF(B2:B10,"<>0"). Only the cells that are not 0 go into the average, and empty cells are skipped as well. Without that criterion, every zero counts as a real value and lowers the result.
Why does AVERAGE return #DIV/0!?
The range has no numbers in it, so Excel ends up dividing by zero. Check for empty cells, text, or numbers stored as text. If an empty result is fine for you, wrap the formula in IFERROR: =IFERROR(AVERAGE(B2:B10),"") shows a blank cell instead of the error.
How do I calculate a weighted average in Excel?
Multiply each value by its weight, add the products and divide by the sum of the weights: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5), with values in B and weights in C. A 10 with weight 2 and a 4 with weight 1 give (20+4)/3, which is 8. A plain AVERAGE would say 7.