SMALL, LARGE and MEDIAN in Excel
Last updated
LARGE returns the k-th largest number in a range and SMALL the k-th smallest: =LARGE(B2:B9,2) gives the second-best sale. MEDIAN returns the middle value of the sorted numbers, so a single huge salary or one lost parcel doesn’t pull it the way it pulls an average.
SMALL, LARGE and MEDIAN syntax
=LARGE(array, k)
- array
- The range of numbers, such as
B2:B9. Empty cells and text are ignored. SMALL takes the same arguments. - k
- The rank to return:
1for the largest (the smallest with SMALL),2for the second, and so on. It can come from a cell, likeD2. - MEDIAN(number1, …)
- MEDIAN takes one or more ranges or numbers, like MIN and MAX, and returns the value in the middle once they are sorted.
Practice SMALL, LARGE and MEDIAN here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find the second-best sale
This quarter’s sales per salesperson are in B2:B9. In E2, return the second highest amount with LARGE.
| Salesperson | Sales ($) | |||
| Olivia Reed | 18,450 | Second-best sale | ||
| James Carter | 22,300 | |||
| Sofia Patel | 15,780 | |||
| Liam Brooks | 26,900 | |||
| Emma Hughes | 19,640 | |||
| Noah Bennett | 24,150 | |||
| Ava Collins | 12,870 | |||
| Ethan Price | 21,030 |
How to use SMALL, LARGE and MEDIAN, step by step
- Click the cell for the result and type
=LARGE(to count from the top, or=SMALL(to count from the bottom. - Select the range of numbers, for example
B2:B9, then type a comma. - Type the rank you want, such as
2, or click the cell that holds it. Lock the range with$if you’ll copy the formula. - Close the parenthesis and press Enter. For the middle value, type
=MEDIAN(B2:B8)instead.
SMALL, LARGE and MEDIAN examples
Second highest value
=LARGE(B2:B9,2)
Returns the second-best sale of the quarter: 24,150.
Second lowest value
=SMALL(B2:B7,2)
Returns the backup quote, 1,325, just above the cheapest one at 1,290.
Middle value
=MEDIAN(B2:B8)
Returns the typical salary, 44,100, where AVERAGE gives 63,700 because of one high salary.
Top 3 with the rank in a cell
=LARGE($B$2:$B$11,D2)
Copied down next to the ranks 1, 2 and 3, it lists the three best sellers: 731, 689 and 604.
Common SMALL and LARGE problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #NUM! | k is 0, negative or larger than the number of values, for example =LARGE(B2:B9,9) on 8 sales. | Use a rank between 1 and the number of values. =COUNT(B2:B9) tells you how many there are. |
| The same value twice | Two values tie, so LARGE returns the same number for rank 1 and rank 2. | That’s expected: tied values each take a rank. Check the data, or break the tie with another column. |
| Wrong values after copying | The range wasn’t locked, so it slid down one row with each copy and lost the top of the list. | Lock it with $: =LARGE($B$2:$B$11,D2). |
| #NAME? | The function name is misspelled (=LARGEST) or written in another Excel language. | Type =LAR and pick LARGE from the list Excel suggests. |
LARGE vs MAX, MEDIAN vs AVERAGE
LARGE with the rank 1 returns the same as MAX, and SMALL with 1 the same as MIN: reach for them when you need the second, third or k-th value. MEDIAN and AVERAGE both describe a typical value, but AVERAGE adds every number, so one salary of 185,000 among salaries around 44,000 drags it far up, while MEDIAN only looks at the middle of the sorted list. Sorting the column shows the same ranking, but it goes stale as soon as the data changes, while a formula stays current.
Frequently asked questions
What’s the difference between MEDIAN and AVERAGE?
AVERAGE adds the values and divides by how many there are. MEDIAN sorts them and takes the one in the middle. With 7 salaries where one is 185,000, the median is 44,100 and the average 63,700.
What does MEDIAN return for an even number of values?
The average of the two middle values: =MEDIAN(1,2,3,10) returns 2.5.
How do I list the top 3 values?
Type 1, 2 and 3 in a column and put =LARGE($B$2:$B$11,D2) next to them, copied down. SMALL gives the bottom 3 the same way.
How do I find the name that goes with the second highest value?
Get the value with LARGE, then look it up with INDEX and MATCH or with XLOOKUP, covered in other lessons of the course.
Keep practising
All 5 SMALL, LARGE and MEDIAN exercises, with explanations and progress tracking.
Open the SMALL, LARGE and MEDIAN practice set