# Thread: Rank Only Specific Values (2000)

1. ## 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. ## 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. ## 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
•