Football Stats Help!

D

dgladney

New Member
Joined
Nov 17, 2021
Messages
14
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi,
Do you have a sample of your dataset? Your question is a bit vague. It's hard to say why your cells aren't lining up properly without looking at it.
 
Upvote 0
Without seeing a sample of your data, it is really hard to give any advice. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

That being said, things like this, which typically fall into the realm of relational databases, tend to work much better in relational database programs like Microsoft Access, which are very dynamically and can easily be queried.
 
Upvote 0
I just started working on this so I don't have a lot of the formulas done. I'm really a novice at excel so excuse any errors. Thanks for at least taking a look.

Spartanburg Script.xlsx
ABCDEFGHIJKLMN
1Team Stats:1900
2Player NamePlayer NumberTouchesCarriesTotal Rush YardsYards per carryThrown toCatchesTotal Recv YdsPass attemptsCompComp %Yds per attYds per comp
3D.Hill3000
4C.Smith400
5M.Heller501#VALUE!0
6C.Lawson600
7A.Washington7000
8T.Byrd800
9M.Kelly9000
10Z.Agnew1100
11K.Kelly1300
12S.Mack1800
13J.Holmes26010
14T.Stephens8000
15
16
17
18
Touch Chart
Cell Formulas
RangeFormula
D1D1=SUM(B3:B17)
F1F1=IF(B3=0,"",C3/B3)
E3E3=SUMPRODUCT(--($A$30:$A$500="H"),--($F$30:$F$500="R"),--($G$30:$G$500=A3),--($M$30:$M$500))
D5,D13,D9,D7D5='Play Call Script'!H4
E5E5=SUMPRODUCT(--('Play Call Script'!F2:F99="R"),--($G$30:$G$500=A3),--($M$30:$M$500))
C3C3=COUNTIF('Play Call Script'!D2:D100, "3")
C4C4=COUNTIF('Play Call Script'!D2:D100, "4")
C5C5=COUNTIF('Play Call Script'!D3:D101, "5")
C6C6=COUNTIF('Play Call Script'!D4:D102, "6")
C7C7=COUNTIF('Play Call Script'!D5:D103, "7")
C8C8=COUNTIF('Play Call Script'!D6:D104, "8")
C9C9=COUNTIF('Play Call Script'!D7:D105, "9")
C10C10=COUNTIF('Play Call Script'!D8:D106, "11")
C11C11=COUNTIF('Play Call Script'!D9:D107, "13")
C12C12=COUNTIF('Play Call Script'!D10:D108, "18")
C13C13=COUNTIF('Play Call Script'!D11:D109, "26")
C14C14=COUNTIF('Play Call Script'!D12:D110, "80")
F3:F14F3=IF(B3=0,"",C3/B3)
 
Upvote 0
Joe4 said:
Without seeing a sample of your data, it is really hard to give any advice. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

That being said, things like this, which typically fall into the realm of relational databases, tend to work much better in relational database programs like Microsoft Access, which are very dynamically and can easily be queried.
Click to expand...
see below...thanks!
 
Upvote 0
dgladney said:
I just started working on this so I don't have a lot of the formulas done. I'm really a novice at excel so excuse any errors. Thanks for at least taking a look.

Spartanburg Script.xlsx
ABCDEFGHIJKLMN
1Team Stats:1900
2Player NamePlayer NumberTouchesCarriesTotal Rush YardsYards per carryThrown toCatchesTotal Recv YdsPass attemptsCompComp %Yds per attYds per comp
3D.Hill3000
4C.Smith400
5M.Heller501#VALUE!0
6C.Lawson600
7A.Washington7000
8T.Byrd800
9M.Kelly9000
10Z.Agnew1100
11K.Kelly1300
12S.Mack1800
13J.Holmes26010
14T.Stephens8000
15
16
17
18
Touch Chart
Cell Formulas
RangeFormula
D1D1=SUM(B3:B17)
F1F1=IF(B3=0,"",C3/B3)
E3E3=SUMPRODUCT(--($A$30:$A$500="H"),--($F$30:$F$500="R"),--($G$30:$G$500=A3),--($M$30:$M$500))
D5,D13,D9,D7D5='Play Call Script'!H4
E5E5=SUMPRODUCT(--('Play Call Script'!F2:F99="R"),--($G$30:$G$500=A3),--($M$30:$M$500))
C3C3=COUNTIF('Play Call Script'!D2:D100, "3")
C4C4=COUNTIF('Play Call Script'!D2:D100, "4")
C5C5=COUNTIF('Play Call Script'!D3:D101, "5")
C6C6=COUNTIF('Play Call Script'!D4:D102, "6")
C7C7=COUNTIF('Play Call Script'!D5:D103, "7")
C8C8=COUNTIF('Play Call Script'!D6:D104, "8")
C9C9=COUNTIF('Play Call Script'!D7:D105, "9")
C10C10=COUNTIF('Play Call Script'!D8:D106, "11")
C11C11=COUNTIF('Play Call Script'!D9:D107, "13")
C12C12=COUNTIF('Play Call Script'!D10:D108, "18")
C13C13=COUNTIF('Play Call Script'!D11:D109, "26")
C14C14=COUNTIF('Play Call Script'!D12:D110, "80")
F3:F14F3=IF(B3=0,"",C3/B3)
Click to expand...
What exactly isn't lining up? Without seeing your "Play Call Script" sheet, I can't verify if your cell references are correct.
 
Upvote 0
I'm sorry...it is below:

Spartanburg Script.xlsx
ABCDEFGHIJ
1Possession #Play NumberPlay CallPlayerYardsPlay TypePlayerTouchesTotal Rush YardsYards per carry
2520R30
3805P40
4265051
5060
6070
7080
8090
90110
100130
110180
120261
130801#VALUE!
Play Call Script
Cell Formulas
RangeFormula
H2H2=COUNTIF('Play Call Script'!D2:D100, "3")
H3H3=COUNTIF('Play Call Script'!D2:D100, "4")
H4H4=COUNTIF('Play Call Script'!D2:D101, "5")
H5H5=COUNTIF('Play Call Script'!D2:D102, "6")
H6H6=COUNTIF('Play Call Script'!D2:D103, "7")
H7H7=COUNTIF('Play Call Script'!D2:D104, "8")
H8H8=COUNTIF('Play Call Script'!D2:D105, "9")
H9H9=COUNTIF('Play Call Script'!D2:D106, "11")
H10H10=COUNTIF('Play Call Script'!D2:D107, "13")
H11H11=COUNTIF('Play Call Script'!D2:D108, "18")
H12H12=COUNTIF('Play Call Script'!D2:D100, "26")
E2:E13E2=MOD(Stats!C5,100)-MOD(Stats!C6,100)
H13H13=COUNTIF('Play Call Script'!D2:D100, "80")
I13I13=SUMPRODUCT(--($F$2:$F$19="R"),--($G$2:$G$100=G6,G8,G4,G12),--($E$2:$E$100))
 
Upvote 0
dgladney said:
I'm sorry...it is below:

Spartanburg Script.xlsx
ABCDEFGHIJ
1Possession #Play NumberPlay CallPlayerYardsPlay TypePlayerTouchesTotal Rush YardsYards per carry
2520R30
3805P40
4265051
5060
6070
7080
8090
90110
100130
110180
120261
130801#VALUE!
Play Call Script
Cell Formulas
RangeFormula
H2H2=COUNTIF('Play Call Script'!D2:D100, "3")
H3H3=COUNTIF('Play Call Script'!D2:D100, "4")
H4H4=COUNTIF('Play Call Script'!D2:D101, "5")
H5H5=COUNTIF('Play Call Script'!D2:D102, "6")
H6H6=COUNTIF('Play Call Script'!D2:D103, "7")
H7H7=COUNTIF('Play Call Script'!D2:D104, "8")
H8H8=COUNTIF('Play Call Script'!D2:D105, "9")
H9H9=COUNTIF('Play Call Script'!D2:D106, "11")
H10H10=COUNTIF('Play Call Script'!D2:D107, "13")
H11H11=COUNTIF('Play Call Script'!D2:D108, "18")
H12H12=COUNTIF('Play Call Script'!D2:D100, "26")
E2:E13E2=MOD(Stats!C5,100)-MOD(Stats!C6,100)
H13H13=COUNTIF('Play Call Script'!D2:D100, "80")
I13I13=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)
 
Last edited:
Upvote 0
BigBeachBananas said:
First, let's address the "Touches" (Column H) on your "Play Call Script". Your formula is currently counting the player number instead of touches. For example, it's counting how many play number 3 are there in column D. Excel looks at column D and sees there's no player #3, so the output is 0. Whereas, There's one player #5 in column D, so excel returns 1. I think what you meant to do is find out how many touches each player did instead. Is this correct?
Click to expand...
yes, that is correct
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Sorting Table + Column
Replies
2
Views
286
rawr19911
R
S
  • Question Question
Hockey Stat Sheet Help!
Replies
1
Views
594
SanjayGMusafir
SanjayGMusafir
D
  • Question Question
Yards per rush calculation
Replies
4
Views
2K
dgladney
D
N
  • Question Question
VBA - Difficult issue to solve for - Any help/direction GREATLY appreciated!
Replies
1
Views
636
MCLIFTO8
MCLIFTO8
D
  • Question Question
Help with Winning Predictors in Rugby Union Matches
Replies
0
Views
446
DG123456
D

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top