Exceljet

Quick, clean, and to the point

How to calculate maximum and minimum values

In this video, we'll look at how to calculate minimum and maximum values in Excel.

Let's take a look.

Here we have a list of properties, that includes an address, price and a variety of other information. Let's calculate the maximum and minimum values.

First, I'm going to created a named range for the prices in this list. You don't need to do this, but it makes the formulas easier to read and copy.

To get the maximum value, use the MAX function.

MAX takes one or arguments, each representing a number or range or numbers. In this case, we just need to supply the named range prices.

To get the minimum price in this list, we need to use the MIN function. Like MAX, MIN takes one or more arguments. As before, we just need to supply the named range "prices".

Now we've calculated have both Maximum and Minimum price.

But what if you want to calculate something like the first, second or third highest price in this list ?

In that case, we'll need some additional functions.

Let's start with largest values. Because we want more than just maximum, we need to use a function called LARGE. The LARGE function takes two arguments, the first argument is an array, which in this case is our named range "prices".

The second argument is called "k". k represents the position, as in first, second, and third largest values.

I'm not sure why Microsoft used k instead of n, because the idea of an nth value is pretty common. But you can think of k like n for nth.

So, if I specify the number 1 for k, we'll get the same number that we calculated with MAX, because the first largest value is the same as the maximum value.

Now I'll go ahead and adjust the formula to use the values in column L for K. Then I can just copy the formula down to calculate the top three prices.

The opposite of the LARGE function is the SMALL function, which calculates the smallest values in an array.

Like LARGE, small also has an argument called "k" that indicates position in the list. So, when K is 1, we are asking for the first smallest value in other words, the minimum value.

With K = 2, SMALL calculates the second smallest value and when K = 3, we get the third lowest price in the list.

You can use any number you like for k, as long as it's less than the total number of values. For example, if I use 10, we'll see  the tenth lowest price in the list.

Course 
Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.