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:
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:
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.
The following tutorials explain how to perform other common tasks in Excel: