Athlete Height Weight Analysis Pivot Table

footballhelmet 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.

Pivot Table Group by 10
Pivot Table Group by 10

Average Weight in Pivot Table

As I suspected, the 60s and 70s are much heavier than their teammates. Only the 90s come anywhere close.

Average Weight in Pivot Table
Average Weight in Pivot Table

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.

Football02

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.
Football04

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.

Football03

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!
_____________

6 thoughts on “Athlete Height Weight Analysis Pivot Table”

  1. Centers get the 50s, Guards 60s, Tackles 70s, and Wide Receivers and Tight Ends get 80s. Forties are fullbacks, 30s and twenties are half backs, and 0s and 10s are quarterbacks. Some of those are in the rules, but others are by convention.

  2. Keep in mind that the BMI was designed as a quick-and-dirty measure, for people who get no exercise. Who’s in better shape, a hockey player who is 5’11?, 210 lb, BMI 29.3, and body fat of 8%, or a hockey fan who is 5’11?, 210 lb, BMI 29.3, and body fat of 28%?

    The units of BMI are pressure (force per unit area) which is nonsense, instead of a more reasonable density (mass per unit volume). A better BMI would use height times waistline squared, rather than height squared.

    In WWII, a team’s linemen tried to enlist in the Navy. According to the height and weight tables, they were too fat. They walked down the street to the Army recruiting office. The clerk ignored the tables, decided these guys were big and strong, and signed them up.

  3. Thanks Dick. I like the math logic in the number assignments for fullbacks, halfbacks and quarterbacks.

    Jon, I’ll take a wild guess and say the hockey player is in better shape. 😉
    The pivot table really showed how skewed the BMI results were for the players. Some of them (60s/70s) appear to be considerably more than 8% body fat though!

  4. The logic in the numbers comes from rules about which players can be in certain places at certain times. Only ends and backs can go downfield to receive a pass, for example. The number coding helps the officials sort this out.
    ___

    While many of the players have more body fat than others (and none are as low as a hockey player), they have so much lean muscle mass, that their BMI still overstates their degree of overweight.

Leave a Reply to Jon Peltier Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.