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.

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.

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 with football player weights and pivot tables.

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!

_____________

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.

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.

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!

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.

Thanks Jon, that makes sense, since there are lots more players to keep track of than in a hockey game.

The math logic that I like is:

Fullback: 40

Halfback: 40/2

Quarterback: 40/4