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
castlerjSenior AnalystAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
If a VBA solution is OK then click the "Rank" button in AH1. Note that it takes ties into consideration.
29068367.xlsm
0
 
Martin LissOlder than dirtCommented:
Ignoring row 3 for a moment, please explain in english why row 4 ranks higher than row 2.
0
 
castlerjSenior AnalystAuthor Commented:
Because it has more points than row 2: 36 vs. 34 (column AG)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
ShumsDistinguished Expert - 2017Commented:
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
0
 
castlerjSenior AnalystAuthor Commented:
Thanks Shums, but the rank is off in column AI.  How do I reverse it?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
castlerjSenior AnalystAuthor Commented:
Right on Subodh!  I did not consider adding column M & S.  What made you go down that path?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Because you wanted to rank with those criteria also. :)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
castlerjSenior AnalystAuthor Commented:
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?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can replace the previously suggested Array Formula with the following Array Formula.
Tested it in the Martin's file.

=IF(OR(NOT($G2>=100%),NOT($M2>=100%),NOT($S2>=100%)),"Not Qualified",(IF(COUNTIF($AG$2:$AG$5,AG2)=1,SUM(($M$2:$M$5>=100%)*($S$2:$S$5>=100%)*($AG$2:$AG$5>$AG2))+1,SUM(($M$2:$M$5>=100%)*($S$2:$S$5>=100%)*($J$2:$J$5>$J2)))))

Open in new window

0
 
castlerjSenior AnalystAuthor Commented:
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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad we could help. :)
0
 
Martin LissOlder than dirtCommented:
Please don't forget to close this question.
0
 
Martin LissOlder than dirtCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here