Avatar of Robert Casaletta
Robert Casaletta
Flag for United States of America asked on

Rank results based on multiple variables

I am ranking (column AH)  the points for a group of members based on if they meet all percentages greater than or equal 100.  My problem is when the point total (column AG) for a non-qualified member is greater than the qualified member.  I cannot figure out how to exclude the non-qualified member from the formula.  I've attached my spreadsheet and my formula is:  

=IF(NOT($G2>=100%),"Not Qualified",IF(NOT($M2>=100%),"Not Qualified",IF(NOT($S2>=100%),"Not Qualified",RANK($AG2,$AG2:$AG4)+SUMPRODUCT(--($AG$2:$AG$4=$AG2),--($D$2:$D$4>$D2)))))
Ranking_EE_111417.xlsx
Microsoft OfficeMicrosoft ExcelMicrosoft ApplicationsSpreadsheets

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Ignoring row 3 for a moment, please explain in english why row 4 ranks higher than row 2.
Robert Casaletta

ASKER
Because it has more points than row 2: 36 vs. 34 (column AG)
Shums Faruk

Hi,

Replace your formula with below for ranking:
=IF(OR($G2<100%,$M2<100%,$S2<100%),"Not Qualified",RANK($AG2,$AG$2:$AG$4)+SUMPRODUCT(--($AG$2:$AG$4=$AG2),--($D$2:$D$4>$D2)))

Open in new window

Add extra column to exclude "Not Qualified" with below formula:
=IFERROR(IF(ISNA(RANK($AH2,$AH$2:$AH$4)),"Not Qualified",RANK($AH2,$AH$2:$AH$4)),"")

Open in new window

Hope this helps.
Ranking_EE_111417.xlsx
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Robert Casaletta

ASKER
Thanks Shums, but the rank is off in column AI.  How do I reverse it?
Subodh Tiwari (Neeraj)

Shouldn't it just be this?
=IF(NOT($G2>=100%),"Not Qualified",IF(OR(NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",SUMPRODUCT(--($M$2:$M$4>=100%),--($S$2:$S$4>=100%),--($AG$2:$AG$4>$AG2))+1))

Open in new window

Robert Casaletta

ASKER
Right on Subodh!  I did not consider adding column M & S.  What made you go down that path?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

Because you wanted to rank with those criteria also. :)
Subodh Tiwari (Neeraj)

Also you can wrap all your Not Qualified conditions within OR criteria like below...

=IF(OR(NOT($G2>=100%),NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",SUMPRODUCT(--($M$2:$M$4>=100%),--($S$2:$S$4>=100%),--($AG$2:$AG$4>$AG2))+1)

Open in new window

Robert Casaletta

ASKER
Got it.  I played with it and came across an issue if there is a tie.  I would like to break the tie based on the point with the larger amount in column J.  Can you help?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Subodh Tiwari (Neeraj)

Maybe this Array Formula which will require confirmation with Ctrl+Shift+Enter instead of Enter alone.
=IF(OR(NOT($G2>=100%),NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",(IF(COUNTIF($AG$2:$AG$4,AG2)=1,SUM(($M$2:$M$4>=100%)*($S$2:$S$4>=100%)*($AG$2:$AG$4>$AG2))+1,SUM(($M$2:$M$4>=100%)*($S$2:$S$4>=100%)*($J$2:$J$4>$J2))+1)))

Open in new window

ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Robert Casaletta

ASKER
Thank you both!! I really liked the VBA usage and can use it in other spreadsheets.  Subodh, thank you for your efforts too and making me think differently.
Subodh Tiwari (Neeraj)

You're welcome! Glad we could help. :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017