MAX and MIN

About this lesson

MAX returns the largest number in a range and MIN the smallest, both skipping empty cells and text.

By the end you can

find the largest and smallest value in a range.
use MAX(cell, 30) as a floor and MIN as a cap.

By Ali, Founder, Excel Lessons

The idea

Two of the simplest functions in Excel. The second way to use them is the one to know. Give MAX a cell and a number, and MAX(C2,30) returns whichever is bigger. That means "C2, but at least 30". MIN(C2,30) means "C2, but at most 30". A minimum charge, a limit on overtime, a discount that cannot go below zero: all three are one of these. People usually write them with an IF that is three times as long.

The mistake: asking MAX which row holds the biggest number. It does not say. It returns the number. Finding the name beside it is a lookup, which is what MATCH is for.

The mistake to watch for

The quiet failure is a range one column too wide. MAX and MIN read every number in the block and return the largest or smallest of all of them. So a MIN meant for the minutes column can return a distance, with no error. Check how many columns the range covers before you trust a result that looks too small.

Where this comes up again

MAXIFS and MINIFS Summaries and Reports
SORT Dynamic Arrays
A Frequency Table: UNIQUE with COUNTIF Dynamic Arrays
SUBTOTAL: One Function, 11 Aggregates Summaries and Reports

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In `E2`, type:

=MAX(B2:B9)

That is the longest run in km.

Row	A	B	C	D
1	Run	Km	Minutes
2	1	5	27	Longest run
3	2	8	44	Quickest run
4	3	10	58	Spread of distances
5	4	6	31
6	5	12	71
7	6	5	26
8	7	14	84
9	8	8	42

Every step

1

In E2, type: =MAX(B2:B9). That is the longest run in km.

Hint. One range, B2 to B9.
2

In E3, find the shortest time with MIN.

Hint. Column C this time.
3

Text inside a range does not confuse MAX. Read =MAX(B1:B9) and say what it returns.

Hint. What is in B1, and does MAX care?
4

In E4, subtract the shortest run from the longest. Shape: =MAX(range)-MIN(range). That is the spread of distances.

Hint. MAX of the range, minus MIN of the range.
5

Run 1 is billed at a minimum of 30 minutes. In E6, use MAX so run 1 is never below 30. Shape: =MAX(cell, value).

Hint. Two arguments: the cell, and 30.
6

A range dragged too wide can pull in the wrong column. Read =MIN(B2:C9) and say what it returns.

Hint. Two columns, one smallest number.