Wednesday, June 22, 2011

NFL Passer Rating on Excel

     In a recent fit of paralyzing obsession, I devoted myself to the task of making an Excel formula that could calculate a quarterback's NFL passer rating (there's actually another formula for the NCAA passer rating, at least according to Wikipedia, that source of heinous lies).
     The formula takes, as input, the quarterback's completion percentage, yards/attempt, touchdowns/attempt (%), interceptions/attempt (%).
     Here's the formula:

=100*(((IF((0.05*(B1-30))<0,0,IF((0.05*(B1-30))>2.375,2.375,(0.05*(B1-30)))))+(IF((0.25*(B2-3))<0,0,IF((0.25*(B2-3))>2.375,2.375,(0.25*(B2-3)))))+(IF((0.2*B3)>2.375,2.375,(0.2*B3)))+(IF((2.375-(0.25*B4))<0,0,(2.375-(0.25*B4)))))/6)

     You need:

     B1 = completion percentage (%)
     B2 = yards/attempt
     B3 = touchdowns/attempt (%)
     B4 = interceptions/attempt (%)

     I thought that it was pretty cool. It's a quick and easy way to evaluate and compare quarterbacks not in the NFL (i.e. quarterbacks without calculated ratings at NFL.com). It was also a good way to learn about Excel conditional logic, which is probably one of those irritatingly "good-to-know" gems.
     Have fun calculating NFL passer ratings! I'll have a research-heavy post up soon.

1 comment:

  1. This formula is wrong. I don't know why but it produces a slightly off result compared to every QB calculator online (which all agree witrh each other.

    ReplyDelete