MAX takes one or more arguments, each representing a number or range of 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 have to supply the named range "prices."
Now we've calculated both a maximum and minimum price.
But what if you want to calculate something like first, second, or third highest or lowest price in the 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 just our named range "prices."
The second argument is called "k"; "k" represents the position, as in first, second, or third largest value.
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.
To make the formula easier to copy, I'll go ahead and adjust the formula to use the values in column L for K. Now I can just copy the formula down to calculate the top three prices.
Like LARGE, SMALL also has an argument called "k" that indicates position in the list. So, when K is "1," we're asking the SMALL function for the first smallest value in the list. In other words, the minimum value.
When K is "2," SMALL calculates the second smallest value, and when K is "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 get the tenth lowest price in the list.
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest...
The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest...