BigTenBoy15
New Member
- Joined
- Feb 24, 2016
- Messages
- 9
I'm trying to average a set of cells based on multiple criteria (hence the AVERAGEIFS), but I want this average to change as more data is added. There may be a very simple fix for this, but I can't figure it out for the life of me. Below is a sample of the table followed by an explanation of what I'm trying to achieve (this is for an NFL model I'm building)
<tbody>
</tbody>
Ideally, I'd like to be able to average the yards of a player over the past X number of weeks that person has played. For example, I'd like to be able to average the yards for the last 3 games that "Me" has played, which would be Weeks 5, 2, and 1 for an average of 166.67. Then, if I change the player's name to "You", the formula would calculate the average for Weeks 5, 4, and 3.
Furthermore, if I add a sixth week, I'd like it to then average Weeks 6, 5, and 2 as those would've been the last three weeks "Me" has played. Hopefully that makes sense and this isn't too complicated, but I appreciate any help!
| Player | Week | Yards | Game Played |
| Me | 1 | 200 | 1 |
| You | 1 | 200 | 1 |
| Me | 2 | 100 | 1 |
| You | 2 | 100 | 1 |
| Me | 3 | 0 | 0 |
| You | 3 | 300 | 1 |
| Me | 4 | 0 | 0 |
| You | 4 | 300 | 1 |
| Me | 5 | 200 | 1 |
| You | 5 | 200 | 1 |
<tbody>
</tbody>
Ideally, I'd like to be able to average the yards of a player over the past X number of weeks that person has played. For example, I'd like to be able to average the yards for the last 3 games that "Me" has played, which would be Weeks 5, 2, and 1 for an average of 166.67. Then, if I change the player's name to "You", the formula would calculate the average for Weeks 5, 4, and 3.
Furthermore, if I add a sixth week, I'd like it to then average Weeks 6, 5, and 2 as those would've been the last three weeks "Me" has played. Hopefully that makes sense and this isn't too complicated, but I appreciate any help!
