Statistical Functions Microsoft Excel · 2 min read
Counting and averages
Function Syntax and example Result Notes AVERAGE =AVERAGE(C2:C9)71.5 Ignores text and blanks, but counts zeros . AVERAGEA =AVERAGEA(range)Counts text as 0 and TRUE as 1. COUNT =COUNT(C2:C9)8 Counts numbers (and dates) only. COUNTA =COUNTA(A1:A9)9 Counts all non-empty cells, text included. COUNTBLANK =COUNTBLANK(A1:D9)0 Counts empty cells. MAX / MIN =MAX(C2:C9) / =MIN(C2:C9)92 / 40 Highest and lowest. MEDIAN =MEDIAN(C2:C9)74.5 Middle value. With an even count it is the average of the two middle values (71 and 78). MODE.SNGL =MODE.SNGL(D2:D9)2500 Most frequent value. Returns the first one when tied. MODE.MULT =MODE.MULT(range)All modes (array result).
Position, rank and percentile
Function Example Result Notes LARGE =LARGE(C2:C9,2)88 2nd largest. SMALL =SMALL(C2:C9,2)55 2nd smallest. RANK.EQ (old RANK )=RANK.EQ(C2,$C$2:$C$9,0)4 Rank of 78. 0 or omitted = descending (largest is 1); 1 = ascending . Ties share a rank. RANK.AVG =RANK.AVG(...)Ties get the average rank. PERCENTILE.INC =PERCENTILE.INC(C2:C9,0.9)89.2 Value below which 90% of data fall. QUARTILE.INC =QUARTILE.INC(C2:C9,1)62.5 Quarter number 0 to 4: 0 = min, 2 = median, 4 = max. PERCENTRANK.INC =PERCENTRANK.INC(range, x)Relative standing from 0 to 1.
Spread and relationship
Function Example Result Notes VAR.S / VAR.P =VAR.S(C2:C9)310.57 Variance of a sample / of the whole population (271.75). STDEV.S / STDEV.P =STDEV.S(C2:C9)17.62 Standard deviation, sample (divides by n - 1) / population (divides by n, gives 16.48). CORREL =CORREL(C2:C9,D2:D9)0.675 Correlation between two sets, from -1 to +1. SLOPE / INTERCEPT =SLOPE(y_range, x_range)Regression line y = mx + c. FORECAST.LINEAR =FORECAST.LINEAR(x, known_y, known_x)Predicts a value on the line. FREQUENCY =FREQUENCY(data, bins)Count in each class interval (array formula).
Need Formula Percentage of total =C2/SUM($C$2:$C$9) (format as %)Percentage change =(New - Old)/OldAverage without a formula Select cells and read the status bar . Simple interest =P*R*T/100Compound amount =P*(1+R/100)^T; =10000*(1+10/100)^2 → 12100 CAGR =(End/Start)^(1/Years)-1; =(150/100)^(1/3)-1 → 14.47%
Memory trick
".S" is Sample, ".P" is Population. The sample version divides by n - 1 , so it is always a little larger.
Keep practising Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us .