Work with MAX, MAXIFS, LARGE, and related functions

- [Instructor] Excel has over 100 different statistical functions. If you're looking for them in going to the Formulas tab, initially you don't see that category here. But it's listed under More Functions and there is Statistical. And as I suggested, quite a few of them, well over 100 here. Now some of the most commonly used ones are Max and Min but there are some others as well, too. If we want to know what the highest compensation amount is in this list, we could certainly sort the data, but we want to keep it in this current order. It's alphabetized by name. We don't really need to sort it anyway but we want to know what the highest compensation amount is.

Equal max. And in this example here we're going to click column E because there's nothing else in column E except those numbers and the title on top. If a list like this had interim totals and a total on the bottom, we'd have to reconsider your use of this. It might not work at all if you've got a lot of totals within it. The example here, we're all set, we don't have to type that right parenthesis. And there we are, that's the highest amount. And its companion is Min. Equal min. What's the smallest amount that we see there? The smallest compensation amount, it's that much.

At different times you might want to know what the second highest or second lowest amount is. And that's using a function called Large. We're looking at the data here, we want to know the second highest. Put in the number two. And if we're looking for the third, we'll put in the three, looking for the fourth, put in the four and so on. So the second largest compensation amount there or second highest, however you say it, there it is. It almost immediately as soon as you've seen this, you know what it's opposite is. It's not Large, it's Small.

So if we're looking for the second smallest, we use the function called Small. And there too, it's got the same flexibility as Large. We're looking for the third smallest, fourth smallest, etc. If you wanted a list of the top five or the bottom five, you could have some numbers set up like this ahead of time. So we could, for example, type equal large, left parethesis, we're looking at column E comma, and this time instead of typing one, two, three, and five or whatever, if we were doing only this cell, this is equivalent to the Max function.

But I'll press control enter so the active cell doesn't move downward, and simply double click right here, and now I've got our first, second, third, fourth, and fifth highest salaries here using that function called Large in a slightly different way. And of course we could do the same kind of thing with Small, going in the opposite direction. Now sometimes you want to combine a couple of thoughts at once. And if you've got Office 365 and you've installed the latest upgrades, in mid 2016 some new functions were introduced. And here too, we've got an upper and a lower version.

Here's a function called MaxIfs. If you are familiar with CountIfs and SumIfs and AverageIfs, you know where this is headed. We want to find the highest compensation amount for anybody who has the job rating of five. Now it might match that 118,000 that we see up top, which was the highest for the list, but we're not sure of that just yet. So we begin by pointing to what it is we're trying to tabulate. That's the data from column E comma. Now based on one, two, three are many many different criteria.

If it's only one criteria, and we sometimes refer to these as a criteria pair, say it's just based on job rating. So we're clicking column D comma five. So what are we about to say? Let's find the highest compensation for those who have a job rating of five. And there it is. And it's different than this number up here so whoever has this compensation number probably has a job rating of four. We're not sure, we could track it down later if we wanted to. But for all of those who have a job rating of five, this is the highest compensation amount.

If we were looking for the highest entry amongst the fours and the fives, instead of a five right here, we would put in double quote greater than three double quote. Or alternately we could put in greater than or equal to four. But now we're looking at the fours and the fives. And there we've got that $118,510 so presumably the highest compensation was someone with a job rating of four. But this is how the construction looks here. And we could have other criteria as well. How about those who have the highest compensation amount with job ratings of more than three and their years of service, that would be column C comma greater than nine double quote greater than nine.

So we can take this a lot of different directions. We can have multiple criteria, in fact we can have up to 127 different pairs like we're seeing here. And it's that same number popping up again. And as soon as you've learned this, you automatically know its opposite. It's going to be MinIfs. I'm going to backtrack here with control z a few times and go back to this entry. That's the highest compensation for those with a job rating of five, and simply without retyping drag this down here.

Double click to edit if we're looking for the lowest compensation amount. That's Min for those who have a job rating of five. That's 22,000. Maybe that's a partial year or maybe it's someone just getting started, we don't know necessarily but that's MinIfs. And it too can have that same mixture of additional criteria pair like we saw in this previous example. Now a slightly different function when it comes to high low and comparison is a function that will tell us what is the most common occurrence in column D.

In other words which job rating is most frequent. And here the function is Mode. This only works with numerical information. What's the most frequent entry in column D? One of the job ratings, which one? The job rating of five. Now how many people have the five or how many entries here have the five? In other words how often does the five occur? And here we use the function called CountIf. And we're simply saying here as we look in column D, how many of these entries have a five.

Now I can either click on the cell above it or put in a five, makes no difference. I'll have an answer here, so 178 different entries there in column D have that job rating of five. Now two other functions more widely used than some of these are Average and Median, and they're not the same, I think most people know that. If you're typing average by the way and you get a little annoyed that you have to type Average and not Avg, if the Autosum button is visible, and it's going to be visible on the left side of the Formulas tab or the right side of the Home tab, in either case there's a tiny little arrow associated with it.

If you click the Drop arrow for Autosum, you will see Average there. You could click that first. Although Excel might be highlighting not the cells you want to average, you can redirect it to highlight these cells right here. Enter. And its companion Median which means the middle value out of a list, it doesn't mean the list has to be sorted, Excel will do that for you in the background, but the median value for this list, and sometimes it's interesting to make a comparison, is this. And by the way, what median does is it takes the list regardless of the order, it sorts the data in the background and picks out the middle value.

Now in this case there are an even number of entries, so what does Excel do? Picks the middle two. Now this list happens to be sorted, that's not a prerequisite or anything like that. The two middle numbers by the way are right here. And what's the average of those two? Look in the bottom here, we see 303,861. That's the number we see here. So this number isn't truly in the list in this case because we have an even number of entries. Imagine these are houses for sale in a given neighborhood and there's one that's quite a bit different than all the others, it distorts the average.

The average is 426,000 but that's more expensive than all these houses here, but certainly not the first one. And median sometimes is a fairer comparison. So we've seen quite a few different examples here of various statistical functions. And certainly some of the most commonly used ones are Max and Min, to a lesser extent perhaps Large and Small, and the newer functions MaxIfs and MinIfs. We also saw the Mode function, and then of course the two very popular and frequently used functions Average and Median.