Statistical Functions¶
Conditional Counting & Summing¶
| Function | Syntax | Example | Description |
|---|---|---|---|
| COUNTA | COUNTA(range) |
=COUNTA(A1:A10) |
Counts non-empty cells |
| COUNTBLANK | COUNTBLANK(range) |
=COUNTBLANK(A1:A10) |
Counts empty cells |
| COUNTIF | COUNTIF(range, criteria) |
=COUNTIF(A1:A10, ">5") |
Counts cells matching criteria |
| COUNTIFS | COUNTIFS(range1, crit1, ...) |
=COUNTIFS(A1:A10, ">5", B1:B10, "<10") |
Counts cells matching multiple criteria |
| SUMIF | SUMIF(range, criteria, [sum_range]) |
=SUMIF(A1:A10, ">0", B1:B10) |
Sums cells where criteria is met |
| SUMIFS | SUMIFS(sum_range, range1, crit1, ...) |
=SUMIFS(C1:C10, A1:A10, "East", B1:B10, ">100") |
Sums with multiple criteria |
| AVERAGEIF | AVERAGEIF(range, criteria, [avg_range]) |
=AVERAGEIF(A1:A10, ">0") |
Average of cells matching criteria |
| AVERAGEIFS | AVERAGEIFS(avg_range, range1, crit1, ...) |
=AVERAGEIFS(C1:C10, A1:A10, "East") |
Average with multiple criteria |
| MAXIFS | MAXIFS(max_range, range1, crit1, ...) |
=MAXIFS(B1:B10, A1:A10, "East") |
Max with criteria |
| MINIFS | MINIFS(min_range, range1, crit1, ...) |
=MINIFS(B1:B10, A1:A10, "East") |
Min with criteria |
Ranking & Position¶
| Function | Syntax | Example | Description |
|---|---|---|---|
| LARGE | LARGE(range, k) |
=LARGE(A1:A10, 2) |
2nd largest value |
| SMALL | SMALL(range, k) |
=SMALL(A1:A10, 3) |
3rd smallest value |
| RANK | RANK(number, range, [order]) |
=RANK(A1, A1:A10) |
Rank of a value (1 = largest) |
| PERCENTILE | PERCENTILE(range, k) |
=PERCENTILE(A1:A10, 0.9) |
90th percentile |
| QUARTILE | QUARTILE(range, quart) |
=QUARTILE(A1:A10, 1) |
1st quartile (25th percentile) |
Central Tendency & Dispersion¶
| Function | Syntax | Example | Description |
|---|---|---|---|
| MEDIAN | MEDIAN(number1, ...) |
=MEDIAN(A1:A10) |
Middle value |
| MODE | MODE(number1, ...) |
=MODE(A1:A10) |
Most frequent value |
| STDEV | STDEV(number1, ...) |
=STDEV(A1:A10) |
Standard deviation (sample) |
| STDEVP | STDEVP(number1, ...) |
=STDEVP(A1:A10) |
Standard deviation (population) |
| VAR | VAR(number1, ...) |
=VAR(A1:A10) |
Variance (sample) |
| VARP | VARP(number1, ...) |
=VARP(A1:A10) |
Variance (population) |
Correlation & Forecasting¶
| Function | Syntax | Example | Description |
|---|---|---|---|
| CORREL | CORREL(array1, array2) |
=CORREL(A1:A10, B1:B10) |
Correlation coefficient |
| FORECAST | FORECAST(x, known_y, known_x) |
=FORECAST(11, B1:B10, A1:A10) |
Predicted y value using linear regression |
Criteria Syntax¶
Criteria arguments support the following patterns:
| Criteria | Meaning |
|---|---|
">5" |
Greater than 5 |
"<=100" |
Less than or equal to 100 |
"<>0" |
Not equal to 0 |
"Apple" |
Exact text match (case-insensitive) |
"App*" |
Wildcard match (starts with "App") |
"???" |
Exactly 3 characters |