Rank Only Specific Values (2000)

Topic

New Reply

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.

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

Thanks so much,
Rich

Reply | Quote

Replies

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 D3:D11

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

Reply | Quote

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

Reply | Quote