How to Use Excel’s New Functions:
MAXIFS and MINIFS
Find Maximum/Minimum Values Specified By Certain Criteria
Microsoft recently released 2 new functions, ‘MAXIFS’ and ‘MINIFS’, that will forever change the way you find minimum and maximum values in a range of cells (based on specific criteria).
The way it used to be:
If we ever needed to find a maximum or minimum value in a range of cells based on specific criteria, we had to create an array formula combining the ‘MAX’ (or ‘MIN’) function with an ‘IF’ function component.
For starters (and clearly), this meant we had to know how to use two different functions together and it also meant we needed to understand how to create an array formula.
While neither of these is too terribly difficult for most Excel users, they are relatively complex for a new or casual user.
“The ‘MAX’ and ‘MIN’ functions in and of themselves find the maximum or minimum value in the range of cells selected”
So whether we select a literal list of values like ‘=MAX(1,2,3,4,5)’ or a range of cell references containing values like ‘=MAX(A2:A6)’, the outcome is the same and no other criteria may be used to filter the results.
However, we can change this by using the ‘IF’ function to impose some criteria on the range being analyzed by the ‘MAX’ (or ‘MIN’) function and act as a filter.
This would look something like ‘=MIN(IF(A2:A6 > 2,A2:A6))’ where we are imposing the criteria that we want the minimum value in the range A2 through A6 greater than 2.
However, this just happens to be an array formula since we are working with a range of cells upon which we are setting criteria.
“Anytime we work with an array formula we much press ‘Ctrl + Shift + Enter’ instead of the usual ‘Enter’ in order for the formula to work correctly.”
However, we sometimes forget to press ‘Ctrl + Shift + Enter’ and this quickly leads to frustration – and to be a bit honest, seems a little outdated (which is why Microsoft created MAXIFS and MINIFS that you’re going to learn about in just a bit…)
The figure below illustrates the previous explanations of the ‘MAX’ and ‘MIN’ functions using the range A2:A6 for our values.
The result of the formulas are in their respective cells in column C and the actual formula view in column D.
“Notice that the formula for ‘MIN’ is enclosed in curly brackets which indicates this is an array formula and is the result of pressing ‘Ctrl + Shift + Enter’”
Since we chose our criteria to be values greater than 2, naturally our result is 3.
Be aware however, if we press ‘Enter’ once we type in this formula, it will not function properly and we will not get the desired results.
Enter ‘MINIFS’ and ‘MAXIFS’
So, we’ve just reviewed the most common way to find minimum or maximum values in a range based on criteria to further drill down into our data.
Let’s turn our attention to a couple of the newest additions to the Excel arsenal of functions that better accommodates these scenarios: ‘MINIFS’ and ‘MAXIFS’
“The ‘MINIFS’ and ‘MAXIFS’ functions now provide ways to perform “filtered” versions of ‘MIN’ and ‘MAX’ within the design of the functions themselves.”
Let’s breakdown the syntax of ‘MINFS’ and MAXIFS’. This is all the elements that must be put into the function for it to return our desired results.
How to use ‘MINFS’ and MAXIFS’ in a real scenario
Let’s take a look at an example where we will seek the minimum and maximum of a range of data within a larger range.
Specifically, we will seek the minimum and maximum scores within each grade range in the following table.
In this scenario, we will move beyond the limitations of the old ‘MAX’ and ‘MIN’ functions and leverage the power of ‘MAXIFS’ and ‘MINIFS’.
We are going to use the new functions to find the minimum and maximum from column A based on criteria in column B.
“This is a small data set, but consider we are looking at a data set of hundreds or more rows and then the value of these functions really become apparent!”
So let’s say we would like to know the maximum and minimum values within a specific letter grade range.
Essentially, what we are looking to do is to filter our data based on a letter grade value in the range of data in column B.
Then, with the data filtered, we seek the minimum and maximum values in column A.
That said, our ‘min_range’ for the ‘MINIFS’ function as well as the ‘max_range’ for our ‘MAXIFS’ function will be the same: A2:A13.
Furthermore, our ‘criteria_range1’ for both will also be the same, B2:B13.
Our worksheet has a cell, D8, where we can input our ‘criteria1’ parameter for our functions, so we will use that cell reference for that argument in both functions.
Obviously, the only difference between the two formulas in this case is the function itself.
“In the following example, we have chosen the grade B as our filter for finding the minimum and maximum values in column A and our formulas return 84 and 88, respectively.”
If we change the letter grade that is our ‘criteria1’ parameter to A, then our formulas will return our maximum and minimum grade values:
The previous two examples scratch the surface of what ‘MINIFS’ and ‘MAXIFS’ are capable of by making use of their ability to drill into data using the criteria parameters.
“Now let’s look at an example that’s a bit more complex! ”
We will consider a table containing baseball player stats that has league, team and position data included for each player.
We will start simple with this data set by pulling maximum and minimum statistics such as batting average.
First, let’s review how to find the maximum and minimum values of one data point, batting average (‘AVG’ in column E).
Again, this is as simple as using the ‘MIN’ and ‘MAX’ functions like below.
In this case, all we are doing is looking for the minimum and maximum value for ‘AVG’ across the entire data set.
“In the following examples we will now begin to drill down into the data which will require us to turn to ‘MINIFS’ and ‘MAXIFS’ in order to insert criteria for filtering the data on things like, league, team, and position.”
Where our first example gave us the overall minimum and maximum for ‘AVG’ in our data set, this next example makes use of the ‘LEAGUE’ column to filter our minimum and maximum results based on two different values, either “American” or “National”.
What we have done now is used the C column ($C$2:$C$117), which is the ‘LEAGUE’ column, as our ‘criteria_range1’ parameter and then set the ‘criteria1’ parameter to the cell reference V11.
This cell contains our choice of “American” or “National” and will now filter the data from which the ‘MINIFS’ and ‘MAXIFS’ will return a value.
Adding a second filter to our ‘MAXIFS’ and ‘MINIFS’
Now we want to take our current scenario a step further and filter the data by team.
Our worksheet table for this example is set up to filter first on ‘LEAGUE’ and then on any ‘TEAM’ within that league.
Note that different teams overall are a part of the two different leagues, so if we choose “National” for league, our choices for team will be limited to those that are within the “National” league.
So, still looking at ‘AVG’ as our stat, we have now filtered the date on “American” as before, but now we are filtering the data further on just “KC” for our team.
This means we have simply added a couple of new arguments to our ‘MINIFS’ and ‘MAXIFS’ formulas.
We have now added the range $B$2:$B$117 as ‘criteria_range2’ and V19 as ‘criteria2’.
These are the column for ‘TEAM’ and the cell reference for our team selection, respectively.
Now our minimum and maximum value results for ‘AVG’ are limited to team “KC” which is limited to the “American” league.
“We can take it even further by building on the previous example and adding one more filter, ‘POS’ or player position.”
Essentially, we will now drill down to minimum and maximum batting average values at the team level based on players that play a particular position like outfield or infield.
All we have to do is add a couple of new arguments to what we already have!
These will simply be ‘criteria_range3’ and ‘criteria3’.
Since we are filtering on ‘POS’ our ‘criteria_range3’ will be the range $D$2:$D$117 since column D is our ‘POS’ column.
Let’s take a look at the results first.
Note that our new parameter for position is in cell V29.
For this example, we now have the minimum and maximum values for batting average for only “IF” (infielders) on team “KC” in the “American” league.
So the lowest batting average among infielders with Kansas City is .256 while the highest is .337.
Notice in the formulas below the additional arguments for our ‘POS’ data range and the cell reference, V29, for the criteria to filter that range on.
“This is a great example of how ‘MAXIFS’ and ‘MINIFS’ can provide a world of flexibility in filtering data by which to find the maximum and minimum values.”
Where this was once a fairly complicated and cumbersome prospect using the old methods, Excel now provides a great deal of power now that we can expand the ability to filter data simply and elegantly.