In the workbook attached, there are two tables namely "Fixtures and Scores" and "Rolling Averages" tables. I need a formula to return the fraction of matches won, drawn or lost in the last 4 or 5 matches played by a team when playing at home, when playing away and the overrall fraction of matches won, drawn or lost. The fraction should be on the last 5 matches (cell K2) and be displayed as a decimal. The expected results for team Arsenal are below the "Rolling Averages" table.

For example, in the last 5 matches played by arsenal at home, they have won 3, drew in 1 game and lost one game. The expected results will therefore be: Wins = 3 / 5 = 0.6, Draws = 1 / 5 = 0.2, Losses = 1 / 5 = 0.2. The last 5 matches played at home should follow the same method.

But in calculating data for the “OVRL” column, the last 5 home and away matches should be considered. I have highlighted matches to be considered for arsenal for ease of understanding and the expected results are below the “Rolling Averages Table”

Note: The interval (4 or 5) should be tied to a specific cell (e.g. K2) so that it can be changed without altering the whole formula. The formulas should self-update when more games are played and more data added to the workbook.

Kip.xlsx