Scoring system for competition

[adurham22926192]

	A	B	C	D	E	F	G	H
1	Number	Total Marks	Points Awarded	Rank	9	Number	Total Marks	Points Awarded
2	163	99	100	1
3	193	98	49	2
4	156	97	24	3
5	98	94	8.5	4
6	179	94	8.5	4
7	62	93	5	6
8	96	92	4	7
9	95	90.5	3	8
10	155	89.5	2	9
11	101	80	1	10
12	105	70	0	11

In Column A is the competitor number.
in Column B is the total marks they received
in column C is the points they were awarded
cooumn d is the rank

basically, i want to show the top nth number of results in columns f to h Based on the number that is in cell E1. In E1, it says 9 so in columns f to h I want the data from a2:c10 to show up as they are the top 9 competitors. But if the number in E1 was 8, I only want the top 8 competitors to show up in F:H. I hope this makes sence! Thanks

 

[adurham22926192]

I think the reason why it wasn’t working is because i used the normal rank formula and there was blank rows in the array. I need a rank formula that will rank it but not say N/A if there are any blank values. Do you know how to do this?

 

[etaf]

put an IFERROR in front
so
RANK(range)
add
IFERROR(RANK(range),"")

=IFERROR(RANK(C2,$C$2:$C$12),"")

as shown here

Book7
	A	B	C	D	E	F	G	H
1	Number	Total Marks	Points Awarded	Rank	4	Number	Total Marks	Points Awarded
2	163	99	100	1		163	99	100
3	193	98	49	2		193	98	49
4	156	97	24	3		156	97	24
5	98	94	8.5	4		98	94	8.5
6	179	94	8.5	4		179	94	8.5
7	62	93	5	6
8	96	92	4	7
9	95	90.5	3	8
10	155	89.5
11	101	80
12	105	70
Sheet1

Cell Formulas
Range	Formula
F2:F12	F2	=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12	G2	=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12	H2	=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
D2:D12	D2	=IFERROR(RANK(C2,$C$2:$C$12),"")