Learning Excel: MIN and MAX
The biggest and the smallest
At the annual general meeting, the plot with the biggest harvest wins a cake. "And who had the worst year?" asks Ruth. "Then we can lend a hand."
MIN and MAX
MAX gives the biggest number in a range, MIN the smallest. D13 holds =MAX(D2:D11): the biggest tomato harvest.
Here too, empty cells don't count. So a plot without tomatoes is not "the smallest, with 0 kilos".
=MAX(D2:D11)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) |
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 |
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 |
| 5 | P04 | 25 | ||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 |
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 |
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 |
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 |
| 12 | Average | |||||
| 13 | Highest | 30.20 | ||||
| 14 |
The second biggest
Want the second or third biggest instead of the biggest? Then you use LARGE, with a second argument: the rank you're after.
=LARGE(D2:D11,2) is the second-biggest tomato harvest. SMALL does the same from the bottom up: =SMALL(D2:D11,1) is the smallest.
=LARGE(D2:D11,2)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | |||
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | 27.90 | ||
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | ||||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | |||
| 5 | P04 | 25 | |||||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | |||
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | ||||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | |||
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | |||
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | ||||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | |||
| 12 | Average | ||||||||
| 13 | Highest | ||||||||
| 14 |
Try it yourself
Who grew the most courgettes? Put the biggest courgette harvest in E13.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) |
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 |
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 |
| 5 | P04 | 25 | ||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 |
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 |
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 |
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 |
| 12 | Average | |||||
| 13 | Highest | |||||
| 14 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson