Find the nth best of a list based on multiple criteria

NeedInformation

New Member
Joined
Feb 23, 2014
Messages
14
Office Version
  1. 2021
Platform
  1. Windows
Trying to figure out the proper index(match()) to find the nth best of a list based on a couple of factors.

For example, if I have an unsorted table with rows of classes with class sizes and average grade in that class. I'd like to find the nth best highest average. Ties will be determine the the largest sized class. Ties beyond that sorted alphabetically.

Using the table below the top 3 classes should return:
  1. Phys.Ed (Highest Grade and no classes with this grade nor class size)
  2. SocialMedia (Next highest grade (95) and the largest class size with that grade)
  3. Band (Next highest grade (95) and although it has the same class size as French (which comes earlier in the list, it is alphabetically first))
I know I can use the large function to find the nth largest but it's the multiple criteria that is stumping me. Should be same as ordering the table by (Grade Average, then Students, then Class name).

Thank you, in advance, for your help!

Class Name (guaranteed unique)StudentsGrade Average
History2075
Algebra2280
French1595
SocialStudies2287
Phys Ed18100
Social Media3295
Art History2075
Government2360
Band1590
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
How about
++Fluff.xlsm
ABCDEFG
1Class Name (guaranteed unique)StudentsGrade Average
2History2075Phys Ed18100
3Algebra2280Social Media3295
4French1595Band1595
5SocialStudies2287
6Phys Ed18100
7Social Media3295
8Art History2075
9Government2360
10Band1595
11
12
Sheet4
Cell Formulas
RangeFormula
E2:G4E2=INDEX(SORT(A2:C10,{3,2,1},{-1,-1,1}),{1;2;3},{1,2,3})
Dynamic array formulas.
 
Upvote 0
Thank you for the quick reply!

If the table were to have hundreds of rows what would be the most concise way of writing out the formula? Assume I'm looking of for the top N where N could be 1 to 10?

Finally, I neglected to add that I'm selecting classes from a specific campus. I tried to edit my post and ran into a timeout issue.

The criteria would be to only look at those classes from given campus. You could add a column D to my prior example and entitle it campus. All could be from the same campus (Say alpha) for the example to work correctly. Assuming Band was at a different campus (beta) then the result of looking for the top 3 from the alpha campus would be:

PhysEd
Social media
French
 
Upvote 0
How about
++Fluff.xlsm
ABCDEFGHI
1Class Name (guaranteed unique)StudentsGrade Average
2History2075AAPhys Ed18100
3Algebra2280A4Social Media3295
4French1595AFrench1595
5SocialStudies2287ASocialStudies2287
6Phys Ed18100A
7Social Media3295A
8Art History2075A
9Government2360A
10Band1595B
11
12
Sheet4
Cell Formulas
RangeFormula
G2:I5G2=INDEX(SORT(FILTER(A2:D100,D2:D100=F2),{3,2,1},{-1,-1,1}),SEQUENCE(F3),{1,2,3})
Dynamic array formulas.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

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
Back
Top