Rank results based on multiple variables

Robert Casaletta
Robert Casaletta used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Ignoring row 3 for a moment, please explain in english why row 4 ranks higher than row 2.
Robert CasalettaManager, Warranty Performance

Author

Commented:
Because it has more points than row 2: 36 vs. 34 (column AG)
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Robert CasalettaManager, Warranty Performance

Author

Commented:
Thanks Shums, but the rank is off in column AI.  How do I reverse it?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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 CasalettaManager, Warranty Performance

Author

Commented:
Right on Subodh!  I did not consider adding column M & S.  What made you go down that path?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Because you wanted to rank with those criteria also. :)
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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 CasalettaManager, Warranty Performance

Author

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?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
If a VBA solution is OK then click the "Rank" button in AH1. Note that it takes ties into consideration.
29068367.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Robert CasalettaManager, Warranty Performance

Author

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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad we could help. :)
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Please don't forget to close this question.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial