Occasionally, a football game appears on the television at my house. I’m talking about real football, with burly men in tight pants, not that other kind of football, with wiry men in shorts.
This weekend, one of the games was between teams from Arizona and New Orleans. While admiring the strategies of the two teams, I noticed that the players whose numbers were in the 60s and 70s seemed heavier than the others.
Was that an optical illusion? Did their numbers, or some extra padding, make them seem bigger? Or were they really larger than the rest of the team?
Excel Weight Analysis
Excel can help you with burning questions such as these. From the NFL site, I copied the player roster for each team, and pasted it into Excel.
Unfortunately, the player heights were entered as feet-inches, e.g. 6-1, so that took a bit of fixing, because Excel helpfully changes numbers in that format to dates. A few players didn’t have numbers listed, so I changed those to zero.
Make a Pivot Table
After the player rosters were pasted into Excel, I created a pivot table from the data.
I put the player numbers into the pivot table Row area, Team name into the Column area, and Weight into the Values area, as Average.
Then I grouped the pivot table Player Numbers into 10s.
Average Weight in Pivot Table
As I suspected, the 60s and 70s are much heavier than their teammates. Only the 90s come anywhere close.
Maybe It’s Not the Number
Admittedly, I don’t know too much about how the player numbers are assigned. Maybe each position gets a specific number range and some positions need bigger players.
So, I created another pivot table, to check the average weight of players in each position, and see which numbers are assigned. A bit of conditional formatting was added, to highlight the different weight ranges.
And it does look like the Guards and Tackles are assigned numbers in the 60s and 70s. The lower numbers have lower weight players, and the 80s have a middle range.
BMI Status
Since we created a Body Mass Index formula a couple of weeks ago, I added that to the player stats too. Then a VLOOKUP formula pulled the BMI status from a lookup table, and a pivot table showed the number of players in each status (Normal, Overweight or Obese).
I’m sure their muscles put the players into different BMI categories than the rest of us, but there aren’t many guys in the Normal category.
Team Weights
Finally, I created a table to compare the player weights on each team. Overall, the Arizona team is a bit heavier. Maybe that slowed them down, and that’s why they lost the game.
Download the Workbook
If you’d like to do your own team weight analysis, you can download the sample workbook from my Contextures website. Go to the Excel Sample Data page, and scroll down to the Football Player section. The zipped file is in xlsx format, and does not contain any macros.
[Update – August 2022]: I’ve added 2022 player data to the workbook, for the same two football teams. Now there are 306 player records for you to analyze!
Anyway, the football season should be over soon, and the players can use the Excel Calorie Counter to get themselves in shape for next year. Only if they want to, of course – I’m certainly not going to mention weight loss to any of them!
_____________
What will you do better in Excel next year? Do you have any Excel bad habits that you’ll break? Are there any Excel skill areas that you’ll improve?
