Spotting MAX and MIN Values

Once you've counted your data, a natural next question is: what's the highest value, and what's the lowest?

What was the best sales day? What's the cheapest item? The lowest test score?

Two simple functions handle this: MAX finds the largest number in a range, and MIN finds the smallest. Let's explore both.

MAX returns the largest number in a range.

Its shape matches the functions you already know:

• =MAX(range)

If A1:A5 holds 120, 95, 200, 150, 80, then =MAX(A1:A5) returns 200, the highest value present.

MIN is the mirror image of MAX. It returns the smallest number in a range.

• =MIN(range)

Using the same data 120, 95, 200, 150, 80, the formula =MIN(A1:A5) returns 80, the lowest value.

Together, MAX and MIN describe the full span of your numbers.

Like COUNT, both MAX and MIN look only at numbers. They quietly ignore:

• Blank cells
• Text values such as "N/A"

So if A1:A5 holds 120, "N/A", (blank), 200, 80, then MAX returns 200 and MIN returns 80. The text and blank cause no trouble.

You track daily temperatures in column B from B2 to B8 for a week: 18, 21, 19, 24, 22, 20, 17.

To find the warmest day: =MAX(B2:B8) returns 24.

To find the coldest: =MIN(B2:B8) returns 17.

Two short formulas instantly summarize the week's extremes.

MAX and MIN handle negative numbers naturally. Remember that a smaller negative is actually lower.

If a range holds -5, 3, -10, 8, then =MAX(...) returns 8 and =MIN(...) returns -10.

This is useful for things like profit-and-loss columns where some values dip below zero.

Subtracting MIN from MAX gives you the spread, the distance between the highest and lowest values.

For temperatures in B2:B8:

• =MAX(B2:B8) - MIN(B2:B8)

With our week of data this returns 24 - 17 = 7 degrees. The spread tells you how variable your data is.

Both functions accept several ranges and individual values, separated by commas.

Say your sales are split across two columns. To find the single best day overall:

• =MAX(A2:A30, C2:C30)

You can even mix in a number, like =MAX(A2:A30, 100), to set a floor the result can't fall below.

You manage a product catalog with prices in column D from D2 to D50.

For a summary box, you want both the cheapest and most expensive item:

• =MIN(D2:D50) shows the lowest price.
• =MAX(D2:D50) shows the highest price.

These two cells now give shoppers an instant price range for your catalog.

MAX and MIN are standard functions that behave the same in Microsoft Excel and Google Sheets.

The formulas =MAX(range) and =MIN(range) return identical results in both tools.

Later you'll meet LARGE and SMALL, which can find the 2nd or 3rd highest value, but for the single biggest and smallest, MAX and MIN are all you need.

MAX and MIN often appear inside larger formulas. A common trick is using MAX to keep a result from going negative.

For example, =MAX(0, A1 - B1) returns the difference between A1 and B1, but never less than zero.

If A1 is 50 and B1 is 70, the raw subtraction would be -20, but MAX pulls it up to 0. This is a tidy way to floor a value.

Test your understanding of MAX and MIN.

You can now pinpoint the extremes in any range. Key points:

• =MAX(range) returns the largest number.
• =MIN(range) returns the smallest number.
• Both ignore blank cells and text, and handle negatives correctly.
• =MAX(range) - MIN(range) gives the spread of your data.
• They accept multiple ranges and values, and work the same in Excel and Sheets.

You've completed the COUNT family and the MAX/MIN pair. Great work counting and summarizing your data.