MIN and MAX in Excel: how to use it
MIN gives the lowest and MAX the highest value in a set of numbers.
Try the first exercises for freeIn the lesson MIN, MAX and AVERAGE
What do MIN and MAX do?
MIN finds the lowest value in a series of numbers, MAX the highest. You give a range, such as C2:C6, and get one number back.
Both functions skip text and empty cells. If you also want to know who or what the number belongs to, combine them with INDEX and MATCH or with XLOOKUP.
When do you use MIN and MAX?
- The highest revenue of the year: =MAX(C2:C13).
- The lowest price in a quote: =MIN(B2:B8).
- Setting a cap, such as a discount of no more than 25: =MIN(A2*10%,25).
- Avoiding a negative result: =MAX(0,B2-C2) never gives less than 0.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The lowest value.
Filled in: =MIN(C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
MIN | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | MIN |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number1 | A cell, a range or a number. | C2:C6 |
[number2] | Optional: more cells or numbers, separated by commas. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
MIN and MAX step by step
E1 contains =MAX(C2:C6). The highest revenue in the list. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the answer should go, here E1, and type MAX with a bracket.
=MAX( - Select the revenue figures, from C2 to C6.
=MAX(C2:C6 - Close the bracket and press Enter: the highest revenue is 1200. For the lowest, replace MAX with MIN.
=MAX(C2:C6)
More examples
The lowest value
E1 contains =MIN(C2:C6). The lowest revenue in the list: the mice in York.
A maximum as a limit
B2 contains =MIN(A2*10%,25). A 10% discount, but never more than 25. MIN picks the smaller of the two: 32 or 25.
Never below zero
C2 contains =MAX(0,A2-B2). The difference is -40. MAX picks the larger of 0 and -40, so you see 0 instead of a negative amount.
Finding the name next to the highest value
MAX only gives you the number. To find out who it belongs to, combine INDEX and MATCH, or use XLOOKUP.
Where it often goes wrong
- Text in a range is skipped.
- For the second highest value, use LARGE.
Practise this in the lesson MIN, MAX and AVERAGE. The first exercises are free.
Frequently asked questions
How do I find the highest value with a condition?
With MAXIFS, available from Excel 2019 onwards.