Rolling/moving average for mixed dat in excel

Hello every one! I am trying to develop a model for simulation of football statistics in excel and I will really appreciate all the help I can get here.

I am attaching a spreadsheet named "Rolling Averages" with the English Premier League results to the 7th Round. There is a table in which the average goals for, the average goals conceded, the team form and team form summary need to be captured.

1. The average goals for and the averages goals conceded should be based on the last five matches played (home and away combined to make last 5 matches)

2. The team form should also be based on the last 5 matches (home and away matches to make last 5).

3. The team form summary is the last part of the table and the formula used here should be in such a way that the section self updates every time new matches are played and results added

4. I need an excel formula that will perform the above commands.

4. The average goals for, averages goals conceded, team form and team summaries should self update everytime new matches are played and new data added to the workbook.

5. The expected results for one of the teams (Arsenal) are also included as a guide.

5. Any modifications to the worksheet to make all the above possible are welcome.

I will appreciate all the help I can get.




Rolling Averages.xlsx