Average deal size for one region (AVERAGEIF)
The problem
Problem statement
The sales deck quotes an average deal size per region. The pipeline is in A2:C7 and the region to report is in E2. Put the average deal size for that region in the Avg deal cell F2, and 0 when the region has no deals at all.
A region with no rows must return 0, not #DIV/0! — the deck cannot show an error.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | F | |
|---|---|---|---|---|---|
| 1 | Region | Deal | Amount | Region | Avg deal |
| 2 | East | Widget | 1200 | East | |
| 3 | West | Gadget | 800 | ||
| 4 | East | Widget | 900 | ||
| 5 | North | Bolt | 1500 | ||
| 6 | East | Gadget | 600 | ||
| 7 | West | Bolt | 400 |
Notes
AVERAGEIF(criteria_range, criteria, average_range) averages only the rows that match. When nothing matches it divides by zero and returns #DIV/0!, which is why the fallback matters: IFERROR turns that into a number the deck can print. AVERAGEIFS is the multi-condition sibling.
How to approach
Your answer goes in F2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is F2
- AVERAGEIF needs the region column, the region, and the amount column
- Think about what happens when no row matches
- Shape: =IFERROR(AVERAGEIF(?, ?, ?), ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: AVERAGEIF with a zero fallback
=IFERROR(AVERAGEIF($A$2:$A$7, E2, $C$2:$C$7), 0)
East has three deals: (1200 + 900 + 600) / 3 = 900. For a region with no rows AVERAGEIF raises #DIV/0!, and IFERROR turns it into 0 so the report stays numeric.
Approach 2: AVERAGEIF alone
=AVERAGEIF($A$2:$A$7, E2, $C$2:$C$7)
The same average without the safety net. Cleaner to read, but it will surface #DIV/0! the first time somebody types a region that has no deals.
Function guide
FAQ
How do I average only the rows that match a condition?
=AVERAGEIF(criteria_range, criteria, average_range). Keep the average range the same size as the criteria range.
Why does AVERAGEIF return #DIV/0!?
No row matched, so it divided by zero. Wrap it in IFERROR(value, 0) when the report needs a number, or use =IF(COUNTIF(...)=0, 0, AVERAGEIF(...)).