Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rank Only Specific Values (2000)

    Hello y'all
    I have a list, example below that I used function RANK, =RANK(Number,Ref,1), to rank from lowest to highest.
    Now I want to only rank those that meet the criteria "Y" and ignore all the "N". Any suggestions?
    I have used an Advanced Filter to extract all the "Y" and the rank the result.
    However, I'd like to rank with one formula (perhaps an IF function?) in the Rank column.
    <pre>Value Acceptable Rank
    2,340,000 Y 2
    2,550,000 Y 3
    2,780,000 N 4
    2,221,000 Y 1
    2,820,000 Y 5
    2,890,000 Y 6
    3,400,000 N 8
    3,100,000 Y 7
    3,500,000 Y 9
    3,512,000 Y 10</pre>

    Thanks so much,
    Rich

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rank Only Specific Values (2000)

    Assuming the items in your sample are A1:C11

    In D2 enter:
    =IF($B$2:$B$11="Y",$A$2:$A$11,"")
    Copy D2 to D311

    in C2
    =IF(B2="Y",RANK(D2,$D$2:$D$11,1),"")
    Copy C2 to C3:C11

    I tried using an array formula, but rank does not seem to be a function which works with array formulas so you need the intermediate column.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank Only Specific Values (2000)

    Dear Steve,

    Thanks so very much. This seems to meet my client's needs perfectly.

    Once the formulas in column D have been entered, you could hide the column to preclude some visual confusion.

    G'Day,
    Rich

Posting Permissions

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