Table of Contents
To find the maximum value of a group of values in Excel, you can use the MAX function. This function calculates the maximum value of a set of numbers or a range of cells. To use the MAX function, enter the formula “=MAX(range of cells)” into a cell, replacing “range of cells” with a list of numbers, or a range of cells. The maximum value of the group of values will then be displayed in the cell.
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.