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.
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
Ranking_EE_111417.xlsx
=IF(NOT($G2>=100%),"Not Qualified",IF(NOT($M2>=100
Ranking_EE_111417.xlsx
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
Replace your formula with below for ranking:
Ranking_EE_111417.xlsx
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)))
=IFERROR(IF(ISNA(RANK($AH2,$AH$2:$AH$4)),"Not Qualified",RANK($AH2,$AH$2:$AH$4)),"")
Ranking_EE_111417.xlsx
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))
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)
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?
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)))
If a VBA solution is OK then click the "Rank" button in AH1. Note that it takes ties into consideration.
29068367.xlsm
29068367.xlsm
You can replace the previously suggested Array Formula with the following Array Formula.
Tested it in the Martin's file.
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)))))
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial