Course outline / Statistical functions
Lesson 43 of 75 · Intermediate · 7 exercises
Everyday statistics
Describe a set of numbers with medians, ranks and more.
What you'll practise
- Find the middle with MEDIAN
- Find the most common with MODE
- Find the second largest
- Find the second smallest
- Rank a value
- Count the numbers
- Round an average
Find the middle with MEDIAN
The median is the middle value once the numbers are sorted. Unlike the average, one huge or tiny number can't drag it around.
=MEDIAN(range)
In cell E2, find the median of the deals in B2:B6.
Find the most common with MODE
The mode is the value that appears most often.
=MODE(range)
In cell E2, find the most common number of deals in B2:B6.
Find the second largest
LARGE finds the 1st, 2nd, 3rd largest value and so on. You give it the range and which position you want.
=LARGE(range, position)
In cell E2, find the 2nd largest value in B2:B6.
Find the second smallest
SMALL is the opposite of LARGE and counts up from the smallest value.
In cell E2, use SMALL to find the 2nd smallest value in B2:B6.
Rank a value
RANK tells you where a number places within a list, with 1 for the biggest.
=RANK(number, list)
In cell E2, find where Lee's deals (B4) rank within B2:B6.
Count the numbers
COUNT counts only the cells that contain numbers, skipping text and blanks. It's a quick way to check how many values you have.
In cell E2, count the numbers in B2:B6.
Round an average
You can wrap one function inside another. Here, work out the average first, then round the result.
=ROUND(AVERAGE(range), 1)
In cell E2, find the average of B2:B6, rounded to 1 decimal place.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.