Average order per region
PremiumIn F2, find the average Sales for the region in E2 with AVERAGEIF, with the data ranges locked so the formula can be filled down.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | Average | ||
| 2 | North | Shoes | 420 | South | |||
| 3 | South | Shirts | 310 | North | |||
| 4 | North | Shirts | 150 | West | |||
| 5 | West | Shoes | 500 | ||||
| 6 | South | Shoes | 260 | ||||
| 7 | North | Shoes | 380 | ||||
| 8 | West | Shirts | 190 | ||||
| 9 | South | Shirts | 240 | ||||
| 10 |
Premium lesson
Unlock Summary tables and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for budgeting and planning
Why this works
This is the pivot table's "Average of Sales". South has three orders, 310, 260 and 240, so the average is 810 / 3 = 270. AVERAGEIF gives the same as the SUMIF total divided by the COUNTIF count, in one step.
Common mistake: Leaving out the third argument. AVERAGEIF then averages the criteria range itself, and region names give #DIV/0!.
The answer
- F2
=AVERAGEIF(1$A$2:$A$9,2E2,3$C$2:$C$9)
In plain words: Average 3the sales in C2:C9 on the rows where 1the region in A2:A9 is 2the region in E2.