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:
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:
SMALL() and LARGE() function syntax
SMALL() and LARGE() have the following syntax:
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)
Join our mailing list
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
my question is why use min and max? in what scenarios is this helpful?