ranking based on criteria

[DMT]

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?

 

[PaddyD]

hi - welcome to the board!

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

Book1
	A	B	C	D
1	Group	Number	Intra-grouprank
2	Y	1	6
3	N	7	6
4	Y	5	2
5	N	8	5
6	Y	3	4
7	N	11	2
8	Y	4	3
9	N	10	3
10	Y	2	5
11	N	9	4
12	Y	6	1
13	N	12	1
Sheet1

 

[Aladin Akyurek]

Book4
	A	B	C	D
1	Y/N	Value	I-Rank	F-Rank
2	No	18
3	Yes	3	13	6
4	No	16
5	Yes	20	1	1
6	No	11
7	No	8
8	No	1
9	Yes	1	14	7
10	Yes	20	1	2
11	Yes	8	9	4
12	No	5
13	No	14
14	Yes	6	11	5
15	No	13
16	Yes	15	5	3
Sheet1

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,"")