Find the minimum or maximum value in a range of cells in Excel

 Excel offers a couple of handy functions that you can use to calculate the smallest and largest values in a range of cells. They are simple functions that go by the names of MIN() and MAX(). This lesson shows you how to use them. It also introduces SMALL() and LARGE(), functions which duplicate what MIN and MAX do, plus more besides. 

MIN() and MAX() function syntax

MIN() and MAX() have the same syntax:

=MIN(value1,value2,value3,…)

=MAX(value1,value2,value2,…)

Essentially, you feed either function a set of values, and the function calculates the minimum or maximum number, depending on which function you are using.

Note that they both ignore anything that isn’t a number. There are a couple of related functions, MINA() and MAXA() which also consider logical values (e.g. TRUE/FALSE) and text numbers. They have the same syntax, although you’ll find that they tend to give the same result.

Unfortunately you can’t use any of these functions to find the smallest text value in a range.

Of course, typing a set of values or cell addresses into a function (which is what the syntax appears to suggest) isn’t exactly the smartest or most efficient way to use them. You would be better to enter a range of cells instead. For example, the following function would work out the minimum value in the range of cells from A1 to A21:

=MIN(A1:A21)

SMALL() and LARGE() function syntax

SMALL() and LARGE() have the following syntax:

=SMALL(array,k)

=LARGE(array,k)

In both cases, array refers to the range of cells to evaluate, and k refers to which value you want, relative to either the smallest or largest values in the range. That means you can find, say, the second smallest number in a range (i.e. set k to 2 in the SMALL function), or the third largest value in a range (set k to 3 in the LARGE function). Building on the MIN example from above, here’s how you’d use the SMALL and LARGE functions to do this:

=SMALL(A1:A21,2) – returns the second smallest value in the cell range A1 to A21.

=LARGE(A1:A21,3) – returns the third largest value in the cell range.

If you wanted to use SMALL and LARGE to find the smallest or largest values, you’d set k to 1 in each case. Obviously you could use MIN and MAX for this, but you may have a situation where k is calculated dynamically as part of the formula, i.e. k might sometimes be 1, and sometimes another value. SMALL and LARGE are more flexible in this case:

=SMALL(A1:A21, calculation to find k)

Lesson Subject – Clever Excel Tricks, Excel Formulas and Functions
Related Lessons – 69, 106