ranking based on criteria

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: ranking based on criteria

  1. #1
    New Member
    Join Date
    Jan 2004
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ranking based on criteria

     
    Hi,

    I have one column containing a list of "Yes" and "No". There is no significance to this, just a means of filtering the list. I have in another column a list of numbers. I would like to rank the numbers in the second column, but only if they are next to a "Yes" cell. ie. can I rank cells based on criteria contained in another column?

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,181
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ranking based on criteria

    hi - welcome to the board!

    Depending on the details (eg what you wwant to do with ties etc), the following might help:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows Windows 2000
    File Edit View Insert Options Tools Data Window Help About
    =

    A
    B
    C
    D
    1
    GroupNumberIntra-group rank
    2
    Y16
    3
    N76
    4
    Y52
    5
    N85
    6
    Y34
    7
    N112
    8
    Y43
    9
    N103
    10
    Y25
    11
    N94
    12
    Y61
    13
    N121
    Sheet1

    [HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ranking based on criteria

      
    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Y/NValueI-RankF-Rank
    2
    No18

    3
    Yes3136
    4
    No16

    5
    Yes2011
    6
    No11

    7
    No8

    8
    No1

    9
    Yes1147
    10
    Yes2012
    11
    Yes894
    12
    No5

    13
    No14

    14
    Yes6115
    15
    No13

    16
    Yes1553
    Sheet1

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Formulas...

    C2 for Intermediate Rank:

    =IF(A2="Yes",RANK(B2,$B$2:$B$16),"")

    D2 for Final Rank:

    =IF(N(C2),RANK(C2,$C$2:$C$16,1)+COUNTIF($C$2:C2,C2)-1,"")

User Tag List

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
  •  

 

 
DMCA.com