Find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I would like to find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values for each user in excel.

Below is a list of the count of trips made by each user ID (user 1, 2, 3, etc. ) to purchase each brand of chocolate.
For example for user 1, he has purchased both Hersheys and Reeses three times (duplicate maximum values). If the Xlookup function is used with the MAX function, then only the brand corresponding to the first MAX value will return, Hersheys, and not Reeses. For user 2 you can see that he has purchased Hersheys, Reeses and Snickers all once. Thus, there are also multiple maximum values in that case also. I would like to return all maximum values if there are multiple occurrences of the maximum value.

Separately, I would also like to return the value for the nth maximum value.

Here is an example data set:
User IDHersheysReesesSnickersTwixCandy Brand with the Highest Number of Trips for User
1​
3​
3​
0​
0​
2​
1​
1​
1​
0​
3​
4​
4​
1​
1​
4​
0​
0​
1​
0​
5​
2​
1​
2​
0​
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I think you have the sentence form nearly correct. We SUM (not COUNT) the cells that have the maximum value, provided there is more than one cell with the maximum value, otherwise return zero.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I think you have the sentence form nearly correct. We SUM (not COUNT) the cells that have the maximum value, provided there is more than one cell with the maximum value, otherwise return zero.
So what I meant is we count the number of values for the output of MAX. Meaning we count the number of 3s in B2:E:2. If this number is greater than 1 (since we only want to sum trips that have more than one maximum value) then sum the values of the trip in the range B2:E2.
 

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Actually I might not be understanding it. Would you mind to please explain in sentence form row three where there are multiple 1s? In this case there is more than one max but the max is not greater than one. So how come that row was summed, as it should have been?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
In this case there is more than one max but the max is not greater than one.
You never said that the number of trips for the max had to be greater than 1, you said the number of brands had to be greater than 1.
trips where the number of candy brands purchased most often is more than one
In row 3 the number of candy brands purchased most often (Reeses, Snickers, Twix) is 3 and that is certainly greater than one.

In sentence form what my formula does is still as I stated above:
We SUM ... the cells that have the maximum value, provided there is more than one cell with the maximum value, otherwise return zero.
 

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
You never said that the number of trips for the max had to be greater than 1, you said the number of brands had to be greater than 1.

In row 3 the number of candy brands purchased most often (Reeses, Snickers, Twix) is 3 and that is certainly greater than one.

In sentence form what my formula does is still as I stated above:
Thank you very much for taking the time to explain! Much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,012
Messages
5,599,337
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top