How to Use MIN/MAX in Google Sheets
=MAX(value1, [value2, ...]) and =MIN(value1, [value2, ...]).
What MIN and MAX do
MAX finds the highest number in the cells you give it, and MIN finds the lowest. They are the quickest way to find the best sale, the lowest price or the earliest and latest values in a column.
Syntax
=MAX(value1, [value2, ...])
=MIN(value1, [value2, ...])
value1: the first value or range to consider.value2, ...(optional): more values or ranges.
Google's help pages for MAX and MIN say each argument must be a cell, a number or a range of numbers. Cells without numbers are ignored. Entering text values directly causes a #VALUE! error. To include text, use MAXA or MINA.
A step-by-step example
Here are five test scores in cells A1:B6.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ann | 82 |
| 3 | Ben | 64 |
| 4 | Cara | 91 |
| 5 | Dev | 77 |
| 6 | Eli | 58 |
To find the highest score:
=MAX(B2:B6)
This returns 91. =MIN(B2:B6) returns 58.
Combine them to get a spread
Subtract one from the other to see the gap between the best and worst score:
=MAX(B2:B6) - MIN(B2:B6)
This returns 33 (91 minus 58).
Common mistakes
- Typing text as an argument. This causes a #VALUE! error.
- Numbers stored as text. They are not treated as numbers.
- Needing the max for one group. To find the maximum that meets a condition, look at MAXIFS (and MINIFS).
- Needing the second highest. Use LARGE or SMALL.
Try it yourself
Use the score table above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Highest score
Write a formula that returns the highest score in B2:B6.
Exercise 2: The spread
Write a formula that returns the difference between the highest and lowest scores in B2:B6.
What to learn next
- LARGE and SMALL: find the nth largest or smallest value.
- AVERAGE: find the mean of a range.
- AVERAGEIF: average the values that meet a condition.
- MEDIAN: find the middle value of a range.
- MAXIFS and MINIFS: the largest and smallest values that meet a condition.
Syntax and rules in this guide were checked against Google's MIN/MAX help page. See all guides.