Hello everyone
I have a problem working out a complicated(ish) formula.
Attached is a spreadsheet with two sheets. One has teams AVERAGE goals scored over the whole season (home and away using Sumproduct). The other sheet has a formula showing the SUM of a teams goals from their last six games. I should add I worked out the former case myself but the latter i've found by looking online.
What I want is to create a formula that will AVERAGE a teams goals over their last six games. Originally I thought DAVERAGE could work but I don't want to specify criteria for all cases: the criteria are simply the home, away and competition in columns B,C and D in 'sheet3' where it looks up the home and away sides current records of avg gls over last 6 prior to matchday. You can see what i'm aiming for in columns J and K from cells 72 onwards in that sheet (in these cells, i have excluded the results from the teams' first game, giving them their records from games 2 to 7; the 72nd cell is the beginning of the respective teams 8th game).
I hope I am explaining the problem clearly. If anyone can assist i'd appreciate it greatly! Thank you.
Bookmarks