AVERAGEIF and AVERAGEIFS practice exercises
Take the average of only the rows that meet one condition, or several with AVERAGEIFS, and leave zeros out of an average.
How AVERAGEIF and AVERAGEIFS work
AVERAGEIF finds the average of the numbers in the rows that meet one condition. It takes three arguments: the range to test, the condition, and the range to average. AVERAGEIFS does the same with two or more conditions.
=AVERAGEIF(A2:A7,"Online",B2:B7) returns the average order value for the rows where column A says Online. With two conditions, =AVERAGEIFS(C2:C9,A2:A9,"North",B2:B9,"Shoes") averages the sales of shoes in the North region. Leave out the third argument and AVERAGEIF averages the tested range itself, so =AVERAGEIF(B2:B8,">0") averages only the values above zero.
Watch the argument order: AVERAGEIF puts the range to average last, but AVERAGEIFS puts it first. If no row meets the condition, you get #DIV/0!, because there is nothing to average. The exercises below cover one condition, two conditions with AVERAGEIFS, and leaving zeros out of an average.
- 1Average one group with AVERAGEIFIn D2, use AVERAGEIF to find the average order value of the Online orders.
- 2Average with two conditions with AVERAGEIFSPremiumIn F1, find the average sale of Shoes in the North region. AVERAGEIFS starts with the range to average, then each range with its condition.
- 3Leave zeros out of an averagePremiumThe store is closed on Mondays and Sundays and records 0 sales. In E1, find the average sales of the days it was open: AVERAGEIF with the condition ">0".
Functions in this topic
Read the guide
Questions
Why does AVERAGEIF return #DIV/0!?
No cell met your condition, so there was nothing to average. Check the criterion for typos and extra spaces. To show something cleaner than the error, wrap the formula in IFERROR, as in =IFERROR(AVERAGEIF(A2:A7,"Online",B2:B7),"No orders").
How do I average the values above a number in a cell?
Join the operator to the cell with &, as in =AVERAGEIF(B2:B20,">"&E1). The comparison stays in quotes and the cell reference stays outside them. Change E1 and the average follows. Use ">="&E1 if the limit itself should count.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF checks one condition and takes the range to average last, and that last argument is optional. AVERAGEIFS checks one or more conditions and takes the range to average first, which is required. Every condition in AVERAGEIFS must be true for a row to be included.