Average deal size without the whale (AVERAGEIF)
The problem
Problem statement
One 50,000 deal is dragging the average away from what a typical deal looks like. Amounts are in B2:B6 and the exclusion threshold is in E1. Put the average of the deals *below* that threshold in the Average cell E2.
Use the threshold from E1 and exclude it — the formula must not name the 50,000 deal directly.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Deal | Amount | Exclude at or above | 10000 |
| 2 | D-301 | 1000 | Average | |
| 3 | D-302 | 1200 | ||
| 4 | D-303 | 900 | ||
| 5 | D-304 | 1100 | ||
| 6 | D-305 | 50000 |
Notes
AVERAGEIF averages only the rows that pass a criteria, so one outlier stops dominating the number. The trap is a plain AVERAGE, which is not wrong mathematically but answers a different question: "what does the mean deal look like when one deal is 20x the rest".
How to approach
Your answer goes in E2.
- Target cell is E2
- AVERAGEIF takes criteria range, criteria, average range
- The criteria is a comparison against E1
- Shape: =AVERAGEIF(?, ?, ?)
FAQ
How do I average values below a threshold?
=AVERAGEIF(range, "<"&threshold, range). The operator is inside the quotes and joined to the cell with &.
Why not just use AVERAGE?
AVERAGE includes the outlier, so a single 50,000 deal moves the number far from a typical deal. Decide which question you are answering.