Spreadsheet Practice

Excel statistical functions

Statistical functions summarize a list of numbers: the typical value, the spread, the top and bottom entries, and how many cells hold data. Most of them skip text and empty cells in a range, which is usually what you want and occasionally the reason a result looks off.

Functions
19
With a graded exercise
All 19
Start with
AVERAGE

Which one should I use?

If you need to…UseWhy
Find the typical valueAVERAGEAdds the numbers and divides by how many there are. Zeros count; blanks and text don't.
Find the typical value when a few numbers are extremeMEDIANThe middle value isn't pulled around by one huge or tiny number the way an average is.
Pull the top or bottom few valuesLARGELARGE and SMALL return the 1st, 2nd, 3rd largest or smallest value, so you can build a top-3 list.
Rank a list from best to worstRANK.EQRANK.EQ gives each value its position, and tied values share a rank.
Measure how consistent the numbers areSTDEVA larger standard deviation means the values are more spread out.
Count cells that hold numbers, anything, or nothingCOUNTACOUNT counts numbers, COUNTA counts any entry, and COUNTBLANK counts the gaps.

Try AVERAGE live

The most useful statistical function, on a sample sheet you can edit. Change a value and watch the results update.

  • F2 =AVERAGE(B2:B7) 35.6 Week 3's zero counts; the empty week 6 is skipped
  • F3 =AVERAGE(B2:B3,B5:B6) 44.5 Two ranges in one formula leave out week 3
  • F4 =ROUND(AVERAGE(B2:B7),0) 36 Rounded to a whole number for a report

Full AVERAGE guide and practice →

All 19 statistical functions

FunctionWhat it doesPractice exercise
AVERAGE functionAVERAGE adds up the numbers you give it and divides by how many numbers there are.Average, best and worst
COUNT functionCOUNT tells you how many cells contain numbers.Counting cells
COUNTA functionCOUNTA counts every cell that isn't empty: text, numbers, dates, errors and even formulas that return empty text.Counting cells
COUNTBLANK functionCOUNTBLANK counts the empty cells in a range: unassigned tasks, unanswered questions, missing dates.Steps left per order
LARGE functionLARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.Top three values
MAX functionMAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature.Average, best and worst
MEDIAN functionMEDIAN returns the middle value once the numbers are sorted.Typical deal size per rep
MIN functionMIN returns the smallest number from a list of values or ranges.Average, best and worst
MODE functionMODE returns the number that appears most often in a list, such as the most common shoe size or order quantity.Most common order quantity
PERCENTILE.EXC functionPERCENTILE.EXC returns the value at a given percentile using the exclusive method, which leaves out the very bottom and top of the range: k must sit strictly between 0 and 1, and close to the ends of a short list it returns #NUM!.Exclusive 20th percentile
PERCENTILE.INC functionPERCENTILE.INC returns the value below which a given share of the data falls: k = 0.9 gives the 90th percentile.Response time target
PERCENTRANK.INC functionPERCENTRANK.INC tells you where a value stands in a list, from 0 for the lowest to 1 for the highest: 0.75 means it is above 75% of the other values.Sales standing
QUARTILE.EXC functionQUARTILE.EXC returns the first, second or third quartile using the exclusive method, which some statistics tools and textbooks use.First quartile, exclusive method
QUARTILE.INC functionQUARTILE.INC returns a quartile of a set of numbers: 1 for the first quartile (the 25th percentile), 2 for the median, 3 for the third quartile, and 0 and 4 for the minimum and maximum.Interquartile range
RANK.EQ functionRANK.EQ tells you where a number places within a list: 1 for the largest by default, or 1 for the smallest if you set the order to 1.Rank the team
SMALL functionSMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.Three lowest bids
STDEV functionSTDEV measures how spread out a set of numbers is, treating them as a sample of a larger group.Which store is most consistent?
STDEV.P functionSTDEV.P measures how spread out a set of numbers is when those numbers are the entire population: every employee, every shift, every student in the class.Output spread per line
VAR functionVAR returns the variance of a sample: the average squared distance of each value from the mean.Demand variance by product

Learn them in order

These functions are taught step by step in the Analysis track. Each lesson ends with graded exercises on real-looking data.

  1. SUM, AVERAGE, MIN, MAX & COUNT7 min
  2. Splitting and counting list cells9 min · Pro
  3. LET8 min · Pro
  4. RANK, LARGE & SMALL9 min · Pro
  5. Median, quartiles & outliers11 min · Pro

Other function groups

Updated Checked by running: every formula on this page was run by our formula engine on .