I am trying to project each players stats for the upcoming week based on there current performance and opponent strength. My issue is with the opponent strength. I will be doing many other starts, but lets start with passing yards. Using Drew Brees as an example, (Projections!C2) he is playing STL this week (Projections!E2). I would like to find out where the teams that have played STL stack up against the rest of the league through the first 7 weeks of the season. Using the NFL schedule for STL ('Schedule Grid'!A30:R30) I would like to look up the first 7 (would like the number of weeks to evaluate to be adjustable based on another cell) teams they play and find the average passing yards these teams have. So for this example, it would be these teams:
PHI,NYG,BAL,WAS,GB,DAL (and will just ignore the BYE week)
These teams had the following average passing yards/game ('O-Pass'!M3:M34):
271.7, 278.2, 223.2, 237.2, 323.4, 295.8
for an average of 271.6ypg
This shows that STL is actually better against the pass then there defensive ypg ('D-Pass'!M3:M34) leads us to believe since they are only letting up 226.5ypg, but there opponents have been getting 271.6ypg.
So basicly I need to look up the players opponent on the schedule and average all the passing yards those teams have gotten up until this point in the season.
Thanks in advance!
Bookmarks