Looking for Football Stats Spreadsheet

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)


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.

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-comfficeffice" /><o></o>
<o></o>
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

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.

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.

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.

How do I download the top table. It is perfect for our booth, so simple. Hope to hear from you gardnertoo. Jim

are these spreadsheets available?

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.

Do you have a football spreadsheet template I can use?