Yards per rush calculation

[dgladney]

Been working on a stats spreadsheet and cant figure out how to calculate total rush yards and yards per carry for the running game. Hopefully someone can help me out. My sheet is below:

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)

 

[Joe4]

Average yards would just be total yards divided by total carries.

But I do not understand the formulas you currently have, especially the formulas in cell I3. It is looking for the value of "H" in column A and "R" in column J, and you have none of that.
The formula does not seem to reflect your actual dat structure.

 

[dgladney]

Average yards would just be total yards divided by total carries.

But I do not understand the formulas you currently have, especially the formulas in cell I3. It is looking for the value of "H" in column A and "R" in column J, and you have none of that.
The formula does not seem to reflect your actual dat structure.

I am a super novice at Excel so I could be totally off. What i want is to figure out how to keep a running total of the yards gained by a player on the run play he touches the ball, and keep a tally of the total yards he ran so I can calculate it for the game. The same for the receiving yards. Hope that makes sense. I just don't know where to start.

 

[Joe4]

I think you are missing a lot of detail here. It looks like there are other sheets at play here ("Play Call Script").
Without knowing the structure of all your data, and what your expected results look like, there is not much we can do to help.

This might be a bit of a challenge to accomplish if you are truly a "super novice Excel user".
You really need to sit with someone and come up with a design plan that would allow you to accomplish all that you want.
You really need someone to walk it through with you every step of the way. That might be too big of an ask from a free forum (which is really just meant to be "question/answer").
That really kind of falls under "Consulting". There are consultants we can recommend, listed here: Consulting Services

 

[dgladney]

I think you are missing a lot of detail here. It looks like there are other sheets at play here ("Play Call Script").
Without knowing the structure of all your data, and what your expected results look like, there is not much we can do to help.

Here are my other tabs...sorry about that:

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

Cell Formulas
Range	Formula
H2:H13	H2	=COUNTIF($D:$D, G2)
J2:J12	J2	=E2/H2
E2:E13	E2	=MOD(Stats!C5,100)-MOD(Stats!C6,100)
I13	I13	=SUMPRODUCT(--($F$2:$F$19="R"),--($G$2:$G$100=G6,G8,G4,G12),--($E$2:$E$100))
J13	J13	=H13/E13

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)