Football Stats Help!

[dgladney]

Hi,

I am an offensive assistant coach and I'm looking for some help on tracking statistics for the offense. I need to capture things like the number of touches our offensive guys get, the number of yards gained per play, completions, passing yards, 3rd down conversions, 4th down conversions, receptions, carries, yards per carry, yards per reception, etc but I'm struggling with getting things lined up properly and the correct formulas added. Any help would be greatly appreciated.

 

[BigBeachBananas]

• 45 minutes ago
• Add bookmark
• #9

I'm sorry...it is below:

Play Call Script
	A	B	C	D	E	F	G	H	I	J
Spartanburg Script.xlsx
1	Possession #	Play Number	Play Call	Player	Yards	Play Type	Player	Touches	Total Rush Yards	Yards per carry
2				5	20	R	3	0
3				80	5	P	4	0
4				26	50		5	1
5					0		6	0
6					0		7	0
7					0		8	0
8					0		9	0
9					0		11	0
10					0		13	0
11					0		18	0
12					0		26	1
13					0		80	1	#VALUE!

Range	Formula
Cell Formulas
H2	H2	=COUNTIF('Play Call Script'!D2:D100, "3")
H3	H3	=COUNTIF('Play Call Script'!D2:D100, "4")
H4	H4	=COUNTIF('Play Call Script'!D2:D101, "5")
H5	H5	=COUNTIF('Play Call Script'!D2:D102, "6")
H6	H6	=COUNTIF('Play Call Script'!D2:D103, "7")
H7	H7	=COUNTIF('Play Call Script'!D2:D104, "8")
H8	H8	=COUNTIF('Play Call Script'!D2:D105, "9")
H9	H9	=COUNTIF('Play Call Script'!D2:D106, "11")
H10	H10	=COUNTIF('Play Call Script'!D2:D107, "13")
H11	H11	=COUNTIF('Play Call Script'!D2:D108, "18")
H12	H12	=COUNTIF('Play Call Script'!D2:D100, "26")
E2:E13	E2	=MOD(Stats!C5,100)-MOD(Stats!C6,100)
H13	H13	=COUNTIF('Play Call Script'!D2:D100, "80")
I13	I13	=SUMPRODUCT(--($F$2:$F$19="R"),--($G$2:$G$100=G6,G8,G4,G12),--($E$2:$E$100))

Click to expand...

In your "Touch Chart" sheet, try into cell C3
=Vlookup(B3, 'Play Call Script'!$G:$H,2,FALSE)
and then use autofill (double click on the bottom right corner of cell C3)

yes, that is correct

 

[dgladney]

In your "Touch Chart" sheet, try into cell C3
=Vlookup(B3, 'Play Call Script'!$G:$H,2,FALSE)
and then use autofill (double click on the bottom right corner of cell C3)

The "Play Call" column will just be text, I'll be typing the play into that cell (which is fine). I would like the "touches" to be the number of times the player in column G touches the ball and to count up, just so I can tell who is getting the ball. I want to add 4 columns so that I can break it down into the touches per quarter (if possible).

 

[dgladney]

The "Play Call" column will just be text, I'll be typing the play into that cell (which is fine). I would like the "touches" to be the number of times the player in column G touches the ball and to count up, just so I can tell who is getting the ball. I want to add 4 columns so that I can break it down into the touches per quarter (if possible).

The "Play Call" column will just be text, I'll be typing the play into that cell (which is fine). I would like the "touches" to be the number of times the player in column G touches the ball and to count up, just so I can tell who is getting the ball. I want to add 4 columns so that I can break it down into the touches per quarter (if possible).

• 45 minutes ago
• Add bookmark
• #9

In your "Touch Chart" sheet, try into cell C3
=Vlookup(B3, 'Play Call Script'!$G:$H,2,FALSE)
and then use autofill (double click on the bottom right corner of cell C3)

That worked! Thanks!!

 

[BigBeachBananas]

The "Play Call" column will just be text, I'll be typing the play into that cell (which is fine). I would like the "touches" to be the number of times the player in column G touches the ball and to count up, just so I can tell who is getting the ball. I want to add 4 columns so that I can break it down into the touches per quarter (if possible).

In H2 Cell, enter:
=COUNTIF($D:$D, G2) and again use autofill
As far as touches per quarter, I don't see a column that indicates which quarter that the touches occurred.

 

[dgladney]

In H2 Cell, enter:
=COUNTIF($D:$D, G2) and again use autofill
As far as touches per quarter, I don't see a column that indicates which quarter that the touches occurred.

Both worked! Thanks. I sent the updated one with the quarters

Spartanburg Script.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Team Stats:							190		0
2	Player Name	Player Number	Touches	Q1	Q2	Q3	Q4	Carries	Total Rush Yards	Yards per carry	Thrown to	Catches	Total Recv Yds	Pass attempts	Comp	Comp %	Yds per att	Yds per comp
3	D.Hill	3	0						0	0
4	C.Smith	4	0							0
5	M.Heller	5	1					1	#VALUE!	0.2
6	C.Lawson	6	0							0
7	A.Washington	7	0					0		0
8	T.Byrd	8	0							0
9	M.Kelly	9	0					0		0
10	Z.Agnew	11	0							0
11	K.Kelly	13	0							0
12	S.Mack	18	0							0
13	J.Holmes	26	1					1		0.038461538
14	T.Stephens	80	2							0.025
15
16
Touch Chart

Cell Formulas
Range	Formula
H1	H1	=SUM(B3:B17)
J1	J1	=IF(B3=0,"",C3/B3)
I3	I3	=SUMPRODUCT(--($A$30:$A$500="H"),--($J$30:$J$500="R"),--($K$30:$K$500=A3),--($Q$30:$Q$500))
H5,H13,H9,H7	H5	='Play Call Script'!H4
I5	I5	=SUMPRODUCT(--('Play Call Script'!F2:F99="R"),--($K$30:$K$500=A3),--($Q$30:$Q$500))
C3:C14	C3	=VLOOKUP(B3, 'Play Call Script'!$G:$H,2,FALSE)
J3:J14	J3	=IF(B3=0,"",C3/B3)

 

[BigBeachBananas]

Both worked! Thanks. I sent the updated one with the quarters

Spartanburg Script.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Team Stats:							190		0
2	Player Name	Player Number	Touches	Q1	Q2	Q3	Q4	Carries	Total Rush Yards	Yards per carry	Thrown to	Catches	Total Recv Yds	Pass attempts	Comp	Comp %	Yds per att	Yds per comp
3	D.Hill	3	0						0	0
4	C.Smith	4	0							0
5	M.Heller	5	1					1	#VALUE!	0.2
6	C.Lawson	6	0							0
7	A.Washington	7	0					0		0
8	T.Byrd	8	0							0
9	M.Kelly	9	0					0		0
10	Z.Agnew	11	0							0
11	K.Kelly	13	0							0
12	S.Mack	18	0							0
13	J.Holmes	26	1					1		0.038461538
14	T.Stephens	80	2							0.025
15
16
Touch Chart

Cell Formulas
Range	Formula
H1	H1	=SUM(B3:B17)
J1	J1	=IF(B3=0,"",C3/B3)
I3	I3	=SUMPRODUCT(--($A$30:$A$500="H"),--($J$30:$J$500="R"),--($K$30:$K$500=A3),--($Q$30:$Q$500))
H5,H13,H9,H7	H5	='Play Call Script'!H4
I5	I5	=SUMPRODUCT(--('Play Call Script'!F2:F99="R"),--($K$30:$K$500=A3),--($Q$30:$Q$500))
C3:C14	C3	=VLOOKUP(B3, 'Play Call Script'!$G:$H,2,FALSE)
J3:J14	J3	=IF(B3=0,"",C3/B3)

Let's look at player #5 as an example. Player #5 has 1 Touch. How do you know which quarter that was in?

 

[dgladney]

Let's look at player #5 as an example. Player #5 has 1 Touch. How do you know which quarter that was in?

I get your point. Let's forget about that for now. I'm still struggling on the running total on the yardage gained rushing/receiving, etc. I really appreciate all your help.

 

[dgladney]

I get your point. Let's forget about that for now. I'm still struggling on the running total on the yardage gained rushing/receiving, etc. I really appreciate all your help.

Spartanburg Script.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Team Stats:			Q1	Q2	Q3	Q4	190		0.333333333
2	Player Name	Player Number	Touches					Carries	Total Rush Yards	Yards per carry	Thrown to	Catches	Total Recv Yds	Pass attempts	Comp	Comp %	Yds per att	Yds per comp
3	D.Hill	3	1						#VALUE!	0.333333333
4	C.Smith	4	1							0.25
5	M.Heller	5	1					1	#VALUE!	0.2
6	C.Lawson	6	1							0.166666667
7	A.Washington	7	1					1		0.142857143
8	T.Byrd	8	1							0.125
9	M.Kelly	9	1					1		0.111111111
10	Z.Agnew	11	1							0.090909091
11	K.Kelly	13	1							0.076923077
12	S.Mack	18	1							0.055555556
13	J.Holmes	26	1					1		0.038461538
14	T.Stephens	80	1							0.0125
15
16
Touch Chart

Cell Formulas
Range	Formula
H1	H1	=SUM(B3:B17)
J1	J1	=IF(B3=0,"",C3/B3)
I3	I3	=SUMPRODUCT(--($J$30:$J$500="R"),--('Play Call Script'!E2:E100=I2),--($Q$30:$Q$500))
H5,H13,H9,H7	H5	='Play Call Script'!H4
I5	I5	=SUMPRODUCT(--('Play Call Script'!F2:F99="R"),--($K$30:$K$500=A3),--($Q$30:$Q$500))
C3:C14	C3	=VLOOKUP(B3, 'Play Call Script'!$G:$H,2,FALSE)
J3:J14	J3	=IF(B3=0,"",C3/B3)