Results 1 to 4 of 4

Thread: Ranking Groups with Multiple Criteria

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Ranking Groups with Multiple Criteria

    A
    B C D E
    GROUP TARGET ACTUAL % ATTAINMENT EXPECTED RANK
    A 9 17 188.9% 1
    A 7 13 185.7% 2
    A 6 11 183.3% 3
    A 12 18 150% 4
    A 6 9 150% 5
    B 1 11 1100% 1
    B 3 9 300% 2
    B 4 8 200% 3
    B 1 2 200%
    B 6 6 100% 4
    C 7 7 100% 1
    C 6 6 100% 2
    C 24 20 83.3%
    C 31 6 19.4%
    D 14 19 135.7% 1
    D 34 37 108.8% 2
    D 24 24 100% 3
    D 26 13 50%

    Hello, I would like to rank a set of data by group and based on multiple criteria. I've given a sample below with the expected output in column E. For each group, I would like to rank by % Attainment (column D), and break ties with Actual (column C). Also, I would like the ranking to skip any line where the % Attainment is less than 100%, or where the Actual is less than 3. Any help would be greatly appreciated!

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Ranking Groups with Multiple Criteria

    Try this, copied down.

    Sheet1

    ABCDE
    1GROUPTARGETACTUAL% ATTAINMENTEXPECTED RANK
    2A917188.90%1
    3A713185.70%2
    4A611183.30%3
    5A1218150%4
    6A69150%5
    7B1111100%1
    8B39300%2
    9B48200%3
    10B12200%
    11B66100%4
    12C77100%1
    13C66100%2
    14C242083.30%
    15C31619.40%
    16D1419135.70%1
    17D3437108.80%2
    18D2424100%3
    19D261350%

    Spreadsheet Formulas
    CellFormula
    E2=IF(OR(D2<1,C2<3),"",SUMPRODUCT(($A$2:$A$19=A2)*(D2+C2/10000<($D$2:$D$19+C$2:C$19/10000))*(D$2:D$19>=1)*(C$2:C$19>=3))+1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 20th, 2019 at 02:06 AM. Reason: Wrong screen shot
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Nov 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Groups with Multiple Criteria

    That worked perfectly! Thank you very much Peter!!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Ranking Groups with Multiple Criteria

    Quote Originally Posted by mwicks View Post
    That worked perfectly! Thank you very much Peter!!
    You're very welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •