FormulaGym
XP 0
Streak 0

The MAX and MIN functions in Excel

MAX returns the largest number in a range. MIN returns the smallest.

What MAX / MIN does

They scan every number in the range for you, which is handy for finding the best sale, the hottest day or the cheapest quote. Empty cells and text are ignored.

Syntax

=MAX(number1, [number2], ...)   =MIN(number1, [number2], ...)
number1
The first number, cell or range to check.
number2, ...
Optional. More numbers, cells or ranges.

Worked example

AB
1DayHigh °C
2Mon14
3Tue19
4Wed17
5Thu22
6Fri16
7Hottest22
8Coolest14
B7 =MAX(B2:B6)
B8 =MIN(B2:B6)

MAX returns 22, the highest high of the week. MIN returns 14, the lowest.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Sales & Ops

Find the best-selling week or the busiest day.

Finance

Spot the largest and smallest invoice.

Data cleanup

Check the range of a column for impossible values.

Common mistakes

  • Expecting MAX to return a name. It returns the number only. To find who or what had the highest value, combine it with a lookup such as INDEX and MATCH.
  • Forgetting that text is ignored. A cell reading n/a is not treated as a low value, it is skipped.
  • Sorting the data instead. A sorted list goes stale as soon as the data changes, while a formula updates itself.

The MAX / MIN course

  1. 1
    Find the highest or lowestFree practice below
    Practise
  2. 2
    Second-highest with LARGEComing soon
  3. 3
    Max by categoryComing soon
  4. 4
    Best and worst of the monthComing soon
  5. 5
    Track records over timeComing soon

Common questions

How do I find the second-largest value?

Use LARGE with a position, for example LARGE(B2:B6, 2). SMALL works the same way for the second-smallest.

Can MAX compare more than one range?

Yes. Separate the ranges with commas, for example MAX(B2:B6, D2:D6).

Related functions