Rank test scores, in descening order but, omitt values below a specified cut-off point

[similoluwa]

Hello,

I need to run a macro to rank test scores in descending order but I would like to omitt values below a specific value, so that those who do not make the cut-off are listed as zero and those who do are included on the rank

 

[jbeaucaire]

Something like:

=IF(A1>50, RANK(A1, $A$1:$A$50), "")

Excel Workbook
	A	B
1	85	8
2	65	12
3	70	11
4	55	14
5	45
6	80	9
7	90	7
8	50
9	105	4
10	40
11	110	3
12	100	5
13	115	2
14	75	10
15	120	1
16	60	13
17	95	6
Sheet4

 

[mancubus]

cell B2:

=IF(A2<5,0,RANK(A2,$A$2:$A$20,0))

copy down to B20.

assumes A1 is header, A2:A20 houses scores, specific value is 5

why macro?

should look at:

http://www.cpearson.com/excel/Rank.aspx

 

[similoluwa]

Thanks a million - both of them worked perfectly xxxxxxxxxxx