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

    a10.25
    a20.65
    a30.47
    b10.60
    b20.68
    b31.00
    c10.52
    c20.08
    c30.63
    d10.17
    d20.15
    d30.31
    e10.42
    e20.11
    e30.19
    f10.02
    f20.28
    f30.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!

  • Cross-posting Without Links


    Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without links to your threads on other forums.


    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.

    :!:Forum Rules

  • 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

    a10.253
    a20.655
    a30.473
    b10.606
    b20.686
    b31.006
    c10.525
    c20.081
    c30.635
    d10.172
    d20.153
    d30.312
    e10.424
    e20.112
    e30.191
    f10.021
    f20.284
    f30.534
  • 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

    a10.2533
    a20.6555
    a30.4733
    b10.6065
    b20.6865
    b31.0065
    c10.5255
    c20.0811
    c30.6355
    d10.1722
    d20.1533
    d30.3122
    e10.4244
    e20.1122
    e30.1911
    f10.0211
    f20.2844
    f30.5344
  • 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!

  • Got it, I posted the other link...sorry about that...


    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!