• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ranking by 3 or more criteria

Kishen

New Member
Hi there. I've been struggling with a problem for almost a week now. I'm trying to assign a rank score to some data based on 3 criteria. The end result should be in a single cell (in fact, I'm using nested if statements to add different combinations, but this is the toughest one).

Criterion 1: Global, Regional or Local (column F)
Criterion 2: Yes or No (column I)
Criterion 3: A score between 1 and 100 (column M)

It works fine when I try to use Criteria 2 and 3 or Criteria 1 and 3, but I'm struggling with all 3 in the same formula.

C1+3 formula is:
COUNTIF($F$3:$F$22,"<"&$F3)+COUNTIFS($F$3:$F$22,$F3,$M$3:$M$22,">"&$M3)+1

C2+3 formula is: COUNTIF($I$3:$I$22,">"&$I3)+COUNTIFS($I$3:$I$22,$I3,$M$3:$M$22,">"&$M3)+1

C1+2+3... need help here.

Thank you :)
 
Hi Kishen,

Welcome to the forum. Please take a few minutes to read the forum best practices to understand why it is always better to upload a sample file with sanitized data.
What is the purpose or objective of the rank? Potentially you can sort an simply have a counter.
 
Hi G,

Thanks for the message. Per your advice, I've uploaded a sanitized version of the file with an empty column for the formula I'm seeking.

The objective of the rank is to allow my team to calculate a priority for a long list of projects based on a set of scores assigned by a project manager to 1 of 3 pillars. This is then weighted based on the %s on the Lookup tab (defining what's important to the managers), and then calculating the rank based on the 3 criteria I mentioned earlier.

Column letters have now changes, so I've updated below.

Criterion 1: Global, Regional or Country (Column C - Scale)
Criterion 2: Yes or No (Column E - Mandatory)
Criterion 3: A score between 1 and 100 (Column I - Impact Score)

Thanks,

Kishen
 

Attachments

  • 20190801 - Sanitised for upload.xlsx
    22.4 KB · Views: 10
A typical formula used to RANK by multiple criteria is

= 1 + COUNTIFS(Classification, "<"&Classification)
+ COUNTIFS(Classification, Classification, Mandatory, ">"&Mandatory)
+ COUNTIFS(Classification, Classification, Mandatory, Mandatory, ImpactScore, ">"&ImpactScore )
 

Attachments

  • 20190801 - Sorted risk (PB).xlsx
    20.5 KB · Views: 11
Last edited:
Peter, why did you CSE? Is it required?

Also the formula should refer to Scale and not classification, if I'm reading the OP's request correctly.
Criterion 1: Global, Regional or Country (Column C - Scale)
Criterion 2: Yes or No (Column E - Mandatory)
Criterion 3: A score between 1 and 100 (Column I - Impact Score)

I've one question to the rank: in this case "C" is before "G" is before "R", thus Country is ranked before Global before Regional. Is this really what is needed? What if I want to rank "Global" ; "Country"; "Regional". What is the logic in applying rank based on alphabetical order having more then 2 values in the list?
 
Guido
Why CSE?
I guess over the years I used CSE so much that I just about forgot that Enter could be used by itself ;)
Now with dynamic arrays, I either need to insert '@' to avoid SPILL errors (no implicit intersection for me) or use CSE.
I have now attempted to restructure the OP workbook to use a Table and uploaded it into my response as an edit. Now I can use the table [@] to pick up the attributes one at a time and have the formula expand dynamically along with the table. It was just that I hesitated to interfere with the OP sample workbook to such an extent.

p.s. I use CSE in 'normal' solutions in order to upload unimportant helper ranges as named formulas.
 
I'd like to echo GraH - Guido's question re Global/Country/Regional.
Could you say in words, rather than with formulae, how you want to go about the ranking?

As an aside, your formula:
Code:
=IF(OR(F3="",G3="",H3=""),"",SUM((F3*INDEX(Lookup!$A$2:$D$4,MATCH($D3,Lookup!$A$2:$A$4,0),2)),G3*INDEX(Lookup!$A$2:$D$4,MATCH($D3,Lookup!$A$2:$A$4,0),3),H3*INDEX(Lookup!$A$2:$D$4,MATCH($D3,Lookup!$A$2:$A$4,0),4))*10)

can be shortened to:
Code:
=IF(OR(F3="",G3="",H3=""),"",SUMPRODUCT($F3:$H3,INDEX(Lookup!$B$2:$D$4,MATCH($D3,Lookup!$A$2:$A$4,0),0))*10)

or possibly to:
Code:
=IF(COUNT($F3:$H3)<>3,"",SUMPRODUCT($F3:$H3,INDEX(Lookup!$B$2:$D$4,MATCH($D3,Lookup!$A$2:$A$4,0),0))*10)
 
A typical formula used to RANK by multiple criteria is

= 1 + COUNTIFS(Classification, "<"&Classification)
+ COUNTIFS(Classification, Classification, Mandatory, ">"&Mandatory)
+ COUNTIFS(Classification, Classification, Mandatory, Mandatory, ImpactScore, ">"&ImpactScore )

Hi Peter. Thank so much for pulling this together for me. First off... I've never seen Excel done in that way before. I love how you've used the table and named ranges... it's not something I'm used to doing, but I see the benefit of it being dynamic.

If I'm trying to avoid using CSE (array formulae)...is there another way I can achieve this?

I'm going to write another post in response to P45cal's request that I explain what I'm trying to achieve.
 
Ok, to translate what I'm trying to achieve... let me see if I can articulate it in words...

Imagine that I have a list of 50 projects that are competing against each other for resources... these projects can be global, regional or country-specific (Global being the most important and local country-specific ones being the least important).

Some of these projects will be mandatory because we have made a commitment to a client or regulatory body.

Finally, each project has been scored on a scale of 1 to 10 (currently subjectively), on it's alignment to our Strategy, Cost Reduction or Risk Reduction.

What I am trying to do is rank these projects to ascertain who should get the resources first.

A Global and Mandatory project with the highest Impact Score should be the top ranked items on the list, while the Local, non-Mandatory projects should be the bottom of the pile.

In my local workbook, I have actually put "switches" to turn on or off the checks for Scale and Mandatory, for scenarios where I only went to see the direct ranking based on Impact score, and then I can individually turn on each switch if I want to see the splits. I'm going to try and learn how to do this the way Peter has done with his tables and named ranges. It might help make the formulae a lot cleaner (and teach me a different way to use Excel)

P45cal, I'm also going to try your shortened versions in a bit... What I've already written is based on lots of reading and copy/pasting, but I'm intrigued that it can be achieved with the shorter method you've suggested... let me see how it works. Thank you :)

I hope this helps you guys understand what I'm trying to achieve...
 
If I'm trying to avoid using CSE (array formulae)…
I have just checked. The version I left up in the previous post, after editing, provided the overall ranking within the table and hence does not use array formulas. The [@[...]] operator in the COUNTIFS function reduces the column references to single values but that is OK because the table propagates the formula throughout the column.

In general, I use Office 365 insider and that allows me to use arrays without CSE. In fact, I bought an additional licence for this year with the sole purpose of being able to use dynamic arrays in order to simplify solutions that would otherwise require the mysteries of relative references and 'copy down' (you are most likely wondering "Has he got that the right way round?" but it is what I mean).

Have you looked at my version of your formula for 'Impact Score'?
= IF( OR( Table1[@[Strategy]:[Risk]]=""), "", 10*SUM(
[@Strategy] * INDEX(Weight, MATCH([@Classification], Classification, 0), 1),
[@Finance] * INDEX(Weight, MATCH([@Classification], Classification, 0), 2),
[@Risk] * INDEX(Weight, MATCH([@Classification], Classification, 0), 3) ) )

It just underlines the problem of allowing someone else anywhere near your spreadsheet ;)!
 

Attachments

  • 20190801 - Sorted risk (PB).xlsx
    20.5 KB · Views: 9
Have you looked at my version of your formula for 'Impact Score'?
= IF( OR( Table1[@[Strategy]:[Risk]]=""), "", 10*SUM(
[@Strategy] * INDEX(Weight, MATCH([@Classification], Classification, 0), 1),
[@Finance] * INDEX(Weight, MATCH([@Classification], Classification, 0), 2),
[@Risk] * INDEX(Weight, MATCH([@Classification], Classification, 0), 3) ) )

Just tried, both with and without CSE. I don't know what I'm doing wrong yet, but I just get a "NULL" value
 
Kishen
#13 You wrote: Sorry, is this a problem? In any case, I haven't received any real responses on the other forum.
Seems that You have missed something ...

Have Your read Forum rules?

Here, part of it
Please Don't
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Do You have any questions about cross-posting?
 
@Kishen
I just get a "NULL" value
Is that in the workbook I posted or when you try to implement the approach in another workbook?
The formula is designed to work within a Table and requires no array calculation.

Each record within the table is evaluated individually. To work outside the table using an array formula, a number of changes would be needed. First, the '@'s would be removed to give whole column references. The OR and SUM functions aggregate over both records and fields so, to aggregated over the fields only, they would need to be replaced by the '+' operation, which give an array result with a value for each record.
 
OK, I've understood your explanation and have a few more questions regarding:
Criterion 1: Global, Regional or Local
Criterion 2: Yes or No
Criterion 3: A score between 1 and 100
Would mandatory projects automatically ALL come at the top? If not, then we have to ascertain what beats what: Does a non-mandatory Global project beat a mandatory local/country one?

Currently your formula seems to try to rank projects directly; I'd use (initailly at least) 2 columns, one to calculate an overall score for the project, another to rank those results with one of the RANK built-in worksheet functions.

In the attached, I've created a couple of lookup tables:
One for the Yes/No mandatory aspect where I chosen a mandatory project to need 10 times the priority of a non-mandatory by giving a score of 1 to non-mandatory projects and a score of 10 to mandatory ones.
For the 'Scale' scoring, I put putatative scores of 10 for global, 5 for regional and 3 for country projects which translates to a global project getting 3.33 times as much priority as country project, twice as much as a regional one, and a regional one getting 1.66 times that of a country one.
I'll multiply these lookup results together (I could add - but I'd have to be more careful relating the three different criteria to one another - which could in fact be an advantage).

So my Overall Score column J would contain the likes of:
=VLOOKUP(E3,$M$1:$N$2,2,FALSE)*VLOOKUP(C3,$M$4:$N$6,2,FALSE)*I3
and then in the adjacent column K, the Overall rank:
=RANK.EQ(J3,$J$3:$J$17)

Now you can play about with the lookup tables scores and see how that affects the overall scores and rankings. (Try not to have have any zeroes as the whole equation will return 0!)
 

Attachments

  • Chandoo42258_20190801 - Sanitised for upload.xlsx
    17 KB · Views: 4
@Kishen

Is that in the workbook I posted or when you try to implement the approach in another workbook?
The formula is designed to work within a Table and requires no array calculation.

@Peter Bartholomew you're right, I tried copying and pasting the formula across to my non sanitised workbook, not realising that there was no table defined in that workbook.

Would mandatory projects automatically ALL come at the top? If not, then we have to ascertain what beats what: Does a non-mandatory Global project beat a mandatory local/country one?

@p45cal yes, you got it right. In essence 1st rank priviledge is to Mandatory project, 2nd to Global, Regional or Country, so a Country specific Mandatory project will outrank a Global Non-mandatory project, even if it's Impact score is lower as well.

On your last post, I'm going to have to use some Zero values, so would need to adapt accordingly... but I was working on this on the side in the mean time and I've come up with something that works I think. Explaining it below.

So, here's what I've done now...

I have replaced the code for the Impact score to the one below (My table is called Calculator and $H3 refer to Classification column
Code:
=IF(COUNT(Calculator[@[Strategy]:[Risk]])<>3,"",SUMPRODUCT(Calculator[@[Strategy]:[Risk]],INDEX(Weighting,MATCH($H3,Classification,0),0))*10)


I have then replaced the code for the Overall Rank with the one below. It's clunky, and I've added 2 Yes/No switches in $F$10 and $I$10 that enable or disable the use of "Region" or "Mandatory" when calculating Rank. Scale is now called Region
Code:
=IF(COUNT(M3)<>1,"",IF(AND($F$10="Yes",$I$10="Yes"),COUNTIFS([Mandatory],">"&[@Mandatory])+COUNTIFS([Mandatory],[@Mandatory],[Region],"<"&[@Region])+COUNTIFS([Mandatory],[@Mandatory],[Region],[@Region],[Impact Score],">"&[@[Impact Score]])+1,IF(AND($F$10="No",$I$10="No"),COUNTIF([Impact Score],">"&[@[Impact Score]])+1,IF(AND($F$10="Yes",$I$10="No"),COUNTIF([Region],"<"&[@Region])+COUNTIFS([Region],[@Region],[Impact Score],">"&[@[Impact Score]])+1,IF(AND($F$10="No",$I$10="Yes"),COUNTIF([Mandatory],">"&[@Mandatory])+COUNTIFS([Mandatory],[@Mandatory],[Impact Score],">"&[@[Impact Score]])+1)))))


I believe it is working now, although I get duplicate ranks if the score is identical (personally think that's ok). Looking forward to your feedback if you think there are any other improvements I can make.

I've sanitised and uploaded a version of my main workbook (maintaining column refs and formulae so you can see what I've done. Thank you all for your fantastic help in getting me this far. Really appreciate it.
 

Attachments

  • 20190805 - Sanitised for upload.xlsx
    23.9 KB · Views: 5
Last edited:
Back
Top