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
- 14
- With a graded exercise
- All 14
- Start with
AVERAGE
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Find the typical value | AVERAGE | Adds 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 extreme | MEDIAN | The middle value isn't pulled around by one huge or tiny number the way an average is. |
| Pull the top or bottom few values | LARGE | LARGE 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 worst | RANK.EQ | RANK.EQ gives each value its position, and tied values share a rank. |
| Measure how consistent the numbers are | STDEV | A larger standard deviation means the values are more spread out. |
| Count cells that hold numbers, anything, or nothing | COUNTA | COUNT 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 skippedF3=AVERAGE(B2:B3,B5:B6) 44.5 Two ranges in one formula leave out week 3F4=ROUND(AVERAGE(B2:B7),0) 36 Rounded to a whole number for a report
All 14 statistical functions
| Function | What it does | Practice exercise |
|---|---|---|
AVERAGE function | AVERAGE adds up the numbers you give it and divides by how many numbers there are. | Average, best and worst |
COUNT function | COUNT tells you how many cells contain numbers. | Counting cells |
COUNTA function | COUNTA counts every cell that isn't empty: text, numbers, dates, errors and even formulas that return empty text. | Counting cells |
COUNTBLANK function | COUNTBLANK counts the empty cells in a range: unassigned tasks, unanswered questions, missing dates. | Steps left per order |
LARGE function | LARGE 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 function | MAX 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 function | MEDIAN returns the middle value once the numbers are sorted. | Typical deal size per rep |
MIN function | MIN returns the smallest number from a list of values or ranges. | Average, best and worst |
MODE function | MODE returns the number that appears most often in a list, such as the most common shoe size or order quantity. | Most common order quantity |
RANK.EQ function | RANK.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 function | SMALL 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 function | STDEV 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 function | STDEV.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 function | VAR 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 Foundations track, which is free. Each lesson ends with graded exercises on real-looking data.
- SUM, AVERAGE, MIN, MAX & COUNT7 min
- LET8 min · Pro
- RANK, LARGE & SMALL9 min · Pro