Learning Excel: AVERAGEIF
An average with a condition
"What does a piece of clothing cost here on average? And is that more expensive than household items?" After counting and adding up: the average with a condition.
AVERAGEIF
AVERAGEIF works like SUMIF: the range with the condition, the criterion and the range with the numbers. Only it gives the average instead of the sum.
=AVERAGEIF(B2:B16,"Clothing",C2:C16) gives £7.67: £46 divided by 6 items.
I2
=AVERAGEIF(B2:B16,"Clothing",C2:C16)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | |||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | 7.666666667 | ||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | ||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | ||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | ||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | ||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | ||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | ||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | ||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | ||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | ||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | ||||
| 13 | Vase | Household | £7.00 | Sat | Grace | ||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | ||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | ||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | ||||
| 17 |
Try it yourself
What did one of Sophie's items cost on average? Work it out in I2.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | ||||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | ||||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | |||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | |||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | |||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | |||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | |||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | |||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | |||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | |||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | |||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | |||||
| 13 | Vase | Household | £7.00 | Sat | Grace | |||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | |||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | |||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | |||||
| 17 |
I2
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson