AVERAGEIF
Average only the values whose row meets a condition.
Syntax
AVERAGEIF(range, criteria, [average_range])| Argument | Required | Description |
|---|---|---|
range | Required | The cells tested against the condition. |
criteria | Required | The condition, such as "East" or ">15". |
average_range | Optional | The cells to average. Omit it to average the tested cells. |
Examples
Every example below was executed against VisiGrid engine 0.31.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=AVERAGEIF(B1:B5,"East",A1:A5) | 20 | The East rows hold 10 and 30 — plus a blank, which is skipped. |
=AVERAGE(A1:A5) | 20 |
Same argument order as SUMIF, same trap
The range being averaged comes last, matching SUMIF
and opposite to AVERAGEIFS, which puts it first.
That reversal between the singular and plural forms is the single most common
mistake in this family.
The defence is the same: use the plural form for everything, and the order stops changing.
Empty matches give an error
If no row satisfies the condition there is nothing to average, and the result is
#DIV/0! rather than zero. That is correct — the mean of nothing is undefined,
not nought — but it needs handling when the condition might legitimately match
nothing.
Excel compatibility
Matches Excel.