Excel Practice
Lessons Lesson 18

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.

Practises MAX MIN LARGE SMALL

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

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, find the longest run in kilometres with MAX.

To begin, type it exactly:

=MAX(B2:B9)

E2
Row A B C D E F G
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. 1

    In E2, find the longest run in kilometres with MAX. To begin, type it exactly: =MAX(B2:B9).

    Hint. One range, B2 to B9.

  2. 2

    In E3, find the shortest time in minutes with MIN.

    Hint. Column C this time.

  3. 3

    Somebody grabbed the heading as well. Read =MAX(B1:B9) and say what it returns.

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

  4. 4

    In E4, work out the spread of the distances: the longest run minus the shortest.

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

  5. 5

    Run 1 is being billed to a club at a minimum of 30 minutes. In E6, write a formula using MAX that shows the minutes of run 1, but never less than 30.

    Hint. Two arguments: the cell, and 30.

  6. 6

    The trap. Somebody wanted the quickest time and dragged the range one column too wide. Read =MIN(B2:C9) and say what it returns.

    Hint. Two columns, one smallest number.