Trying to rank data based on certain criteria

[dw300]

I am statistician for football team. I have created a list of team stats for the entire team, but am wanting to create separate lists for individual categories such as rushing and receiving, but only include those individual players with at least one attempt. The following is a sample from the team list for rushing yards.

Player Name	Attempts	Yards	Average	Touchdowns	Longest Rush
Player A	9	24	2.7	0	8
Player B	26	185	7.1	6	27
Player C	2	62	31.0	2	52
Player D	4	12	3	0	5
Player E	0	0	0	0	0
Player F	26	216	8.3	1	29
Player G	2	-2	-1	0	1
Player H	0	0	0	0	0
Player I	1	5	5	0	5

<tbody>
</tbody>

To create a list to Rank them by Yards I have tried the following.

cell B12: =IFERROR(LARGE($C$2:$C$10,ROW(1:1)),"")
cell B13: =IFERROR(LARGE($C$2:$C$10,ROW(2:2)),"") ect. Through cell B20.

cell A12: {=IF(LEN(B12),INDEX($A$2:$A$10,SMALL(IF($C$2:$C$10=B12,ROW($1:$9)),COUNTIF(B$12:B12,B12))),"")}
Through cell A20.

RESULT:

Player F	216
Player B	185
Player C	62
Player A	24
Player D	12
Player I	5
Player E	0
Player H	0
Player G	-2

<tbody>
</tbody>

IS there a way, in which the condition, ATTEMPTS (B2:B10) must be greater than zero, can be added to the formula's in A12:B20 to remove Player E and Player H from the result? (only include players with at least 1 attempt in the result)

 

[Eric W]

Try this in B12:

=IFERROR(LARGE(IF($B$2:$B$10>0,$C$2:$C$10),ROW(1:1)),"")

confirmed with Control+Shift+Enter, and drag down.

 

[dw300]

Thank you Eric for the reply. Your answer worked for the for A12 and B12 and down through A20 and B20... removing Player E and Player H from the result. However, a new issue has come up if I edit my original table to show Player H with the following.

Player H

1

0

0

0

0

<colgroup><col width="91" span="6"></colgroup><tbody>
</tbody>

He now has 1 attempt, but still for zero yards. He should now be included in the result. When I start with B12 and drag the formula, it includes the zero in B18. Player E still has zero attempts/yards and is still not included in column B. However, for column A, instead of showing Player H in cell A18 it returns Player E, because it is the first result matching the zero yards in the original table. Need to add a condition to A12 and down to return only results with at least 1 attempt to match the result in Column B12 to B20.
{=IF(LEN(B12),INDEX($A$2:$A$10,SMALL(IF($C$2:$C$10=B12,ROW($1:$9)),COUNTIF(B$12:B12,B12))),"")}
{=IF(LEN(B18),INDEX($A$2:$A$10,SMALL(IF($C$2:$C$10=B18,ROW($1:$9)),COUNTIF(B$12:B18,B18))),"")}

RESULT:

	Player F 216 Player B 185 Player C 62 Player A 24 Player D 12 Player I 5 Player E 0 Player G -2 <colgroup><col style="mso-width-source:userset;mso-width-alt:3328; width:68pt" width="91" span="2"> </colgroup><tbody> </tbody>
Player E, should be Player H. Thanks in advance.

<tbody>
</tbody>

 

[Eric W]

B12:

=IF(LEN(B12),INDEX($A$2:$A$10,SMALL(IF(($C$2:$C$10=B12)*($B$2:$B$10>0),ROW($1:$9)),COUNTIF(B$12:B12,B12))),"")

with Control+Shift+Enter.

 

[dw300]

Again, Thank you Eric.
So far this is working as needed, but would like to add one more condition, if possible. IF I revise the original table to the following (below)... in which player C and player H each have the same amount for "YARDS". Would now show the result for "Player Name" in A12, "Attempts" in B12, and "Yards" in C12 and drag all down to row 20. In the current form, the formula would find Player C and list him first (row 14) and then Player H next (row 15). Can the formula's in A12 and B12 be revised to show the player with more attempts listed first in the resulting list? Therefore, Player H would be shown ahead of Player C.

(A12) =IF(LEN(C12),INDEX($A$2:$A$10,SMALL(IF(($C$2:$C$10=C12)*($B$2:$B$10>0),ROW($1:$9)),COUNTIF(C$12:C12,C12))),"")
(B12) =IF(LEN(C12),INDEX($B$2:$B$10,SMALL(IF(($C$2:$C$10=C12)*($B$2:$B$10>0),ROW($1:$9)),COUNTIF(C$12:C12,C12))),"")
(C12) =IFERROR(LARGE(IF($B$2:$B$10>0,$C$2:$C$10),ROW(1:1)),"")

Player Name	Attempts	Yards	Average	Touchdowns	Longest Rush
Player A	9	24	2.7	0	8
Player B	26	185	7.1	6	27
Player C	2	62	31.0	2	52
Player D	4	12	3	0	5
Player E	0	0	0	0	0
Player F	26	216	8.3	1	29
Player G	2	-2	-1	0	1
Player H	10	62	6.2	0	22
Player I	1	5	5	0	5

<tbody>
</tbody>

 

[Eric W]

Try this:

	A	B	C	D	E	F	G
1		Attempts	Yards	Average	Touchdowns	Longest Rush
2	Player A	9	24	2.7	0	8
3	Player B	26	185	7.1	6	27
4	Player C	2	62	31.0	2	52
5	Player D	4	12	3.0	0	5
6	Player E	0	0	#DIV/0!	0	0
7	Player F	26	216	8.3	1	29
8	Player G	2	-2	-1.0	0	1
9	Player H	10	62	6.2	0	22
10	Player I	1	5	5.0	0	5
11
12	Player F	26	216	8.3	1	29	7
13	Player B	26	185	7.1	6	27	3
14	Player H	10	62	6.2	0	22	9
15	Player C	2	62	31.0	2	52	4
16	Player A	9	24	2.7	0	8	2
17	Player D	4	12	3.0	0	5	5
18	Player I	1	5	5.0	0	5	10
19	Player G	2	-2	-1.0	0	1	8
20

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A12 =IF($G12="","",INDEX(A:A,$G12)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G12 {=IFERROR(MOD(LARGE(IF($B$2:$B$10>0,$C$2:$C$10*10000+$B$2:$B$10+ROW($B$2:$B$10)/100),ROWS($G$12:$G12)),1)*100,"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

When I need to pull multiple columns from a table, it's usually better to have one column have a formula that returns the row number matching the right row. This means you only need 1 calculation-intensive array formula instead of several. Then just use INDEX for the other columns. Copy B12 over to F, and down as far as needed.

You might also consider just sorting the table, or copying it somewhere and sorting it. If you sort by yards, then attempts, you should get the same results.