Looking for Football Stats Spreadsheet

kooltxguy

New Member
Joined
Mar 25, 2009
Messages
3
I do color for a local High School Football on the radio. I am looking for a simple spreadsheet to track stats during the game. Any help would be appreciated. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
To capture the action during play, I would lay out columns in the following order, based on how early in a play you will have the relevant info:

A: team with possession at the start of the play
B-c: Ball on what yardline. B="OWN" or "OPP", C=number
D: Down
E: Distance to go

All of which you will know before the snap. Next columns:

F: Offensive play type (Pass, Run, PUnt, etc.) Use abbreviations that make sense to you, and use them consistently. You may want more detail, such as SCreen, OPtion, DRaw, etc. As long as you are consistent.
G: Player. Receiver for a pass play, ball carrier for a rushing play.
H: Passer. This is important if a team uses more than one QB in a game.

Those are arranged in roughly the order they happen. Now for the play results:

I: Complete/Incomplete pass
J: Defensive play type (Tackle, Sack, Forced Fumble, Interception, Blocked pass/punt/kick...) Again, use abbreviations and be consistent.
K: Defensive player who made the tackle/interception/forced the fumble...
L: Yards attempted (for incomplete passes) or Yards In Air for completed passes (we can use this later to calculate yards-after-catch)
M: Yards gained. Use negative numbers for losses.
N: Points scored on play
O: Turnover type (D=turnover on downs, F=fumble, I=int)

And finally, the items that happen after the play is whistled dead:

P-R: offensive penalty (Player number in P, infraction in Q, yards assessed in R, with zero yards meaning declined)
S-U: defensive penalty (same pattern)
kooltxguy.xls
ABCDEFGHIJKLMNOPQRSTU
26PU=PuntS=sackD=downs
27H=HomeP=PassC=CompleteT=tackleI=int
28V=VisitorR=RunI=IncompleteB=Blocked pass/punt/kickF=fumbleplayertypeyardsplayertypeyards
29TeamBall onDownYds to goO playball carrierPasserComplete/IncompleteD PlayD PlayerYds in airPlay YdsPointsT/O typeO PenaltyD Penalty
30HOWN20110R34T664
31HOWN2426R23T87-2
32HOWN2238R34T8712
33HOWN34110P1811CT441234
34HOPP32110P1911IB448
35HOPP32210P1811CT4689
36HOPP2331P2334I3
Sheet1



Then comes the fun part: calculating the stats during the game. By separating the play into 21 columns (so far), we can perform calculations against lots of individual stats, using primarily the SUMPRODUCT function. We can, for example, determine how many rushing plays have gone to player ##, how many yards total he has rushing, how many yards he has receiving, number of pass attempts to him, yards per touch, etc. Because we recorded the down, we can calculate 3rd down conversion rate, 4th down conversion, number of first downs, etc. If we add a "Game Clock" column, we can calculate length of drive, time of possession, etc. On the other side of the ball, we can calculate a given defensive player's number of tackles, sacks, etc. If we add a set of cells somewhere else to hold "Season to date" and "Per Game Average" numbers, we can add/compare today's game numbers to those for tracking things like a player nearing a school/conference/state record, or if today's numbers represent a season high or personal best, etc.
kooltxguy.xls
ABCDEFGHIJKL
1Team stats:3144.66666666742434250.00%10.7521.5
2Home Player NumberCarriesTotal Rush YardsYards per carryThrown toCatchesTotal Recv YdsPass attemptsCompComp %Yds per attYds per comp
311      3266.67%9.33333333321.5
418   2243     
519   1       
639           
7231-2-21       
8342168   1  3 
945           
Sheet1
 
Upvote 0
Thank you so much for your help.

The second example worked great but the first one must not have been linked correctly because I cannot tell what the formulas are for each. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
The only thing I see missing is a way to determine the number of rushes/receptions + total yardage for an individual player.

If you would like to e-mail me your reply here is my address
removed
 
Last edited by a moderator:
Upvote 0
First thing: You don't ever want to include your email address in plaintext in your posts. There are automated "bots" out there harvesting email addresses off the web. To protect you from spammers, the MrExcel board moderators will probably have removed it for you by the time you get this, but keep it in mind for future posts.

The first spreadsheet snapshot contains no formulas. That's simply a range of empty cells where you will be typing as the plays happen. I put it in the post to both show the proposed layout and to include the fictional data that my formulas were crunching, so you could see the formulas and verify manually that they came up with the right answers. (The data for player 18 really is 2 catches, for total play yardage of 34+9=43.)

To get total yardage for a given player, in the cells shown in the second snapshot a new column would be needed, adding columns C and G. So in cell M3 the formula would be =C3+G3. I also didn't include any of the formulas you'll need for defensive stats, things like number of tackles, etc; and I've only shown the home team's stat calculations.

What I pictured was having the calculation rows at the top of the sheet (you'll note that the stats in my post are on rows 1 through 9). The in-game data would be below that (I randomly chose row 30 for the first play). By using the "Freeze Window" feature, the stats rows would stay visible as you move down the play-by-play range, so you won't have to scroll up to read the stats after entering a fourth quarter play way down on row 200. This is a time when a big monitor will be almost a neccesity. In order to minimize the number of rows displayed above the "Freeze Window" break, you might limit the stats calculation to a small set of key players instead of the entire roster.
 
Upvote 0
Thanks again. I am new at this Message Board stuff. I will remember next time to not include my e-mail. I have layed out the spreadsheet the way you have it.
 
Upvote 0
After you've done a game, let me know how it worked for you. If you need help with other stats, I would be happy to help.
 
Upvote 0
Can anyone tell me if I can download the spreadsheets above or how to access the formulas in the second sheet? The instructions say to click on the hyperlink in the cell, but when I do that I am directed back to the main page.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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