Abstract
Transcript
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, a price, and a variety of other information. Let's calculate the maximum and minimum values in this list.
First, I'm going to create a named range for the prices in the list. You don't need to do this, but it makes the formulas easier to read and copy.
To get a maximum value, use the MAX function.
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.
The opposite of the LARGE function is the SMALL function which calculates the smallest values in a list.
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.
