MIN and MAX in Excel: find the extremes and calculate the range

Read the sign before choosing the lowest value

A demonstration temperature log contains -4, -1, 0 and 3 degrees Celsius, with a fifth reading not entered. Put these values in B2:B6 and labels Check 1 through Check 5 in A2:A6. Leave B6 empty.

The smallest reading is -4, not -1: farther below zero means smaller on the number line. The largest is 3. These are invented readings for learning the formulas, not operating limits for equipment.

Use MIN for the smallest recorded number

MIN returns -4 for the example range. It considers the numeric zero in B4 and ignores the genuinely empty B6. You do not need to sort the rows first.

The result is a value, not a row number or a check label. If two checks share the lowest reading, MIN still returns one number; it does not tell you which checks produced it.

=MIN(B2:B6)

Use MAX for the largest recorded number

MAX returns 3. Change B5 from 3 to -2 and the maximum becomes 0, the value in B4. If you then clear B4, every remaining reading is negative and the maximum becomes -1.

That sequence makes the role of zero visible. An empty cell does not force a zero maximum when the range already contains negative numbers. A recorded zero does participate in the comparison.

=MAX(B2:B6)

Calculate the distance between the extremes

The statistical range is maximum minus minimum. In the initial example, it is 3 minus -4, or 7 degrees. Subtracting a negative number increases the difference.

Do not call 7 the average temperature. The range describes the distance between the extremes and uses no information about how the other readings are distributed. The word range can also mean a group of spreadsheet cells; here it means this numerical difference.

=MAX(B2:B6)-MIN(B2:B6)

Check that there are readings to summarize

If all five input cells are empty, both MIN and MAX return 0. That function result does not prove a temperature of zero was recorded. COUNT on the same range returns 0 and reveals the absence of numeric observations.

The guarded expression below assumes the inputs are numbers, blanks or ordinary notes and contain no spreadsheet errors. It reports No readings when there are no numbers. Error cells require separate attention: an error-only range also has a zero COUNT and would receive this message.

=IF(COUNT(B2:B6)=0,"No readings",MAX(B2:B6)-MIN(B2:B6))

Do not overinterpret a zero range

A single recorded value has the same minimum and maximum, so its range is 0. Multiple identical readings also have range 0. Neither tells you how many checks were completed.

Show the observation count beside the extremes when completeness matters. For this exercise, one reading of -2 gives minimum -2, maximum -2 and range 0, while four initial readings give minimum -4, maximum 3 and range 7.

Try changes at both ends

After resetting, enter 9 in the previously empty B6. MAX rises to 9 and the range becomes 13; MIN stays -4. Change B2 from -4 to -6 and the range becomes 15.

When an unexpectedly large range appears in your own worksheet, inspect both extreme source values before drawing a conclusion. A misplaced sign or inconsistent unit can create a real formula result from bad input. Keep every reading in the same unit.

Further reading

• Microsoft: MIN function
• Microsoft: MAX function