Lesson 7: Select a statistic that answers the question
The mean is sensitive to extreme values; the median is the middle observation. Sample functions estimate a population from observed data, while population functions treat the listed values as the entire population.
For 10, 12, 13, 15, 100, the average is 30 but the median is 13. Report both when outliers matter.
Statistical formula reference
27 formulas shown
| Function | Purpose | Example |
|---|---|---|
| AVERAGE / AVERAGEA | Mean; AVERAGEA also evaluates text and logical values in references. | =AVERAGE(B2:B20). |
| MEDIAN | Middle value of an ordered set. | =MEDIAN(B2:B20). |
| MODE.SNGL | Most frequent value. | =MODE.SNGL(B2:B20). |
| MODE.MULT | Returns multiple modes as an array. | =MODE.MULT(B2:B20) spills in Microsoft 365. |
| LARGE / SMALL | Returns the kth largest or smallest value. | =LARGE(B2:B20,2) returns second-highest. |
| RANK.EQ | Ranks a number; ties share a rank. | =RANK.EQ(B2,$B$2:$B$20,0). |
| RANK.AVG | Ranks ties using their average rank. | =RANK.AVG(B2,$B$2:$B$20,0). |
| PERCENTILE.INC | Percentile including endpoints. | =PERCENTILE.INC(B2:B20,0.9). |
| PERCENTILE.EXC | Percentile excluding endpoints. | =PERCENTILE.EXC(B2:B20,0.9). |
| QUARTILE.INC / EXC | Returns a selected quartile using inclusive or exclusive method. | =QUARTILE.INC(B2:B20,3) returns Q3. |
| STDEV.S | Sample standard deviation. | =STDEV.S(B2:B20). |
| STDEV.P | Population standard deviation. | =STDEV.P(B2:B20). |
| VAR.S / VAR.P | Sample and population variance. | =VAR.S(B2:B20). |
| CORREL | Pearson correlation coefficient for paired values. | =CORREL(B2:B20,C2:C20). |
| COVARIANCE.S / P | Sample or population covariance. | =COVARIANCE.S(B2:B20,C2:C20). |
| SKEW | Sample skewness, a measure of distribution asymmetry. | =SKEW(B2:B20). |
| KURT | Excess kurtosis statistic. | =KURT(B2:B20). |
| COUNT / COUNTA / COUNTBLANK | Count numeric, non-empty, or blank cells. | =COUNT(B2:B20). |
| COUNTIF / COUNTIFS | Count values matching one or several conditions. | =COUNTIF(B2:B20,">=70"). |
| FORECAST.LINEAR | Predicts a y value from a linear trend. | =FORECAST.LINEAR(A21,B2:B20,A2:A20). |
| TREND | Returns y values along a linear trend line. | =TREND(B2:B20,A2:A20,A21:A25). |
| LINEST | Returns regression statistics for a linear model. | =LINEST(B2:B20,A2:A20,TRUE,TRUE). |
| FREQUENCY | Counts values in bins; returns an array. | =FREQUENCY(B2:B20,D2:D5). |
| CONFIDENCE.NORM | Confidence interval width using normal distribution. | =CONFIDENCE.NORM(0.05,STDEV.S(B2:B20),COUNT(B2:B20)). |
| NORM.DIST | Normal distribution probability or density. | =NORM.DIST(x,mean,standard_dev,TRUE). |
| BINOM.DIST | Binomial distribution probability. | =BINOM.DIST(3,10,0.5,FALSE). |
| POISSON.DIST | Poisson distribution probability. | =POISSON.DIST(2,4,TRUE). |
Use statistics responsibly
Correlation describes association, not causation. Check that paired ranges have the same length and aligned observations. State units, sample size, and the population/sample assumption beside a result.
Progress stays in this browser.