Often you may want to find the max value of some dataset in Excel based on a category or group.
For example, suppose we have the following dataset and we’d like to find the max value of “points” for each team:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Find the Unique Groups
Next, we need to use the =UNIQUE() function to produce a list of unique team names.
In our example, we’ll type the following formula in cell D2:
=UNIQUE(A2:A16)
This will produce a list of unique teams:
Step 3: Find the Max Value by Group
Next, we will use the following formula to find the max points scored by each team:
=MAX(IF(A:A=D2,B:B))
We will type this formula into cell E2 and then copy and paste it down to the remaining cells in column E:
This tells us:
- The max points scored by players on the Mavs is 26.
- The max points scored by players on the Warriors is 19.
- The max points scored by players on the Lakers is 33.
- The max points scored by players on the Heat is 19.
- The max points scored by players on the Celtics is 29.
Note: To calculate the minimum points scored by each team, simply replace the MAX in the formula with MIN.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count by Group in Excel
How to Calculate the Sum by Group in Excel
How to Calculate a Five Number Summary in Excel