Statistical & Conditional Counting
Conditional Average with OR Criteria & Calculated Arrays (AVERAGE + FILTER)
Calculate conditional averages when AVERAGEIFS falls short—such as OR conditions across regions or row-level gross margin percentages computed on the fly without a helper column.
Two cases where AVERAGEIFS fails—and AVERAGE(FILTER(...)) succeeds
Built-in AVERAGEIFS has two structural limitations: (1) all criteria are strictly AND-joined, so you cannot average deals in ("North" OR "West") in a single call—and averaging two separate AVERAGEIFS calls produces an unweighted average of averages!—and (2) its range arguments only accept worksheet ranges, rejecting calculated array expressions like (Revenue - Cost) / Revenue.
Combining AVERAGE(FILTER(values, boolean_mask)) solves both problems cleanly: use + for OR conditions, * for AND conditions, and wrap in IFERROR(..., "NO_MATCH") when no rows satisfy the filter.
| Analytical Goal | Why AVERAGEIFS Fails | AVERAGE + FILTER Solution |
|---|---|---|
| Region is North OR West | Averaging two AVERAGEIFS skews unequal sample sizes | AVERAGE(FILTER(Rev, (Reg="North")+(Reg="West"))) |
| Row-level Margin % where Rev >= 1000 | Calculated array (Rev-Cost)/Rev rejected as range | AVERAGE(FILTER((Rev-Cost)/Rev, Rev>=1000)) |
| Empty filter protection | AVERAGE of #CALC! / empty throws error | IFERROR(ROUND(AVERAGE(FILTER(...)), 4), "NO_MATCH") |
OR-Criteria & Calculated-Margin AVERAGE(FILTER) Formula
Multiply boolean masks for AND, add boolean masks (> 0) for OR, and wrap AVERAGE in ROUND and IFERROR.
=IFERROR(ROUND(AVERAGE(FILTER(C2:C7, ((B2:B7 = "North") + (B2:B7 = "West")) * (C2:C7 >= 1000))), 2), "NO_MATCH")Try the example
Use the original values or change them to test your understanding.