# Complicated Ranking based off of multiple conditions

• Hey there,

I'm trying to create a ranking and quintile off of multiple criteria (consider these columns a, b, c, d, e)

Essentially I want to do the following

1. Create a ranking *(Column D) based off of the Usage value (column C) by item (Column B) against how each location (Column A) perform for that particular item (Column B)

2. I then would likely create a quintile ranking (Column E) of numbers (1,2,3,4,5) based of of those ranking values

The formula for this is way over my head and i'm not sure it's even possible! Help Please [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.mrexcel.com\/forum\/images\/smilies\/icon_smile.gif"}[/IMG2]

obviously there is much more data than this, Its normally around 200K rows, this is just a snipit example that I would hopefully be able to drag the formula's down for

I have the same question here https://www.excelforum.com/exc…le-variables-formula.html

 a 1 0.25 a 2 0.65 a 3 0.47 b 1 0.6 b 2 0.68 b 3 1 c 1 0.52 c 2 0.08 c 3 0.63 d 1 0.17 d 2 0.15 d 3 0.31 e 1 0.42 e 2 0.11 e 3 0.19 f 1 0.02 f 2 0.28 f 3 0.53
• In D2 try:

=SUMPRODUCT(--(\$A\$2:\$A\$19=A2),--(C2<\$C\$2:\$C\$19))+1

and in E2 try:

=MATCH(D2,PERCENTILE(\$D\$2:\$D\$19,{5,4,3,2,1}/5),-1)

Where there is a will there are many ways. Finding one that works for you is the challenge!

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[COLOR="#FF0000"]No further help to be offered, please, until the OP has complied with this request.[/COLOR]

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Got it, I posted the other link...sorry about that...Hey NBVC (That worked, but it didn't compare any of the items in column B)
It looks like it ranked and quintiled off of each location based on usage, but what I need to do, and where i'm stumped...Is to rank/quintile off of each item compared to each locations unique item performance

For example to show on the ranking

 a 1 0.25 3 a 2 0.65 5 a 3 0.47 3 b 1 0.6 6 b 2 0.68 6 b 3 1 6 c 1 0.52 5 c 2 0.08 1 c 3 0.63 5 d 1 0.17 2 d 2 0.15 3 d 3 0.31 2 e 1 0.42 4 e 2 0.11 2 e 3 0.19 1 f 1 0.02 1 f 2 0.28 4 f 3 0.53 4
• I am not sure how you want to include column B, it seems they are just consecutively numbered item numbers in each group.

Maybe you can repost your sample with expected results in both columns D and E.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Appreciate the quick response! So, column B are examples of unique item numbers...

each of the usage values (column C) are by that item number (column B) at that location (column A)

1. I have to rank each item numbers (column B) Usage (Column C) as against how the other locations (Column A) performed for that same item

2. Same theory would apply in number 1, but ranked by quintile (finance terms)

1 - top 0-20% (rank number 1)
2 - 20-40% (rank number 2)
3 - 40-60% (rank number 3)
4 - 60-80% (rank number 4)
5 - 80-100% (rank numbers 5 and 6)

Keep in mind that there is a whole lot more data in my workbook and this is just an example...so think that there may be 500+ ranking numbers, hence why ranking into quintiles in column E is important

Does that make sense?

For example to show on the ranking

 a 1 0.25 3 3 a 2 0.65 5 5 a 3 0.47 3 3 b 1 0.6 6 5 b 2 0.68 6 5 b 3 1 6 5 c 1 0.52 5 5 c 2 0.08 1 1 c 3 0.63 5 5 d 1 0.17 2 2 d 2 0.15 3 3 d 3 0.31 2 2 e 1 0.42 4 4 e 2 0.11 2 2 e 3 0.19 1 1 f 1 0.02 1 1 f 2 0.28 4 4 f 3 0.53 4 4
• For the Rank, to get same results you have I used this formula:

=SUMPRODUCT(--(\$B\$2:\$B\$19=B2),--(C2>\$C\$2:\$C\$19))+1

for the Quintile, maybe I am not sure what I am doing, but best I can get is to use this Array* formula which seems reasonable, but not same as your result:

=MATCH(\$C2,PERCENTILE(IF(\$B\$2:\$B\$19=B2,\$C\$2:\$C\$19),{5,4,3,2,1}/5),-1)

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

I've seen same post in several sites, without your links to other sites you posted on... you should also show links here to all other posts you made in other sites...

Where there is a will there are many ways. Finding one that works for you is the challenge!

• So I tried that and It doesn't pull down correctly...I think it has to do with the fact that it isn't referencing anything in column A (The location)...Let me ask this differently (See below)

I would need to rank each item performance (Column B) based off of its usage (column C) relative to those same items (Column B) from other locations (Column A)

So for example below (Location 1, Item 3 has a usage of 20%, which puts it at a rank of 2 as it is the 2nd lowest of that certain item as it compares to how each store is using that item)

Does that help?

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"excel help.PNG","data-attachmentid":1200645}[/ATTACH]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!