Running average

Question

Hi, 
I have trouble calculating a running average in Google sheets. 
Please see the attached file. 
In cell H2, I want to calculate the last 3-games average GD for the team at cell G2.
So I need a formula that finds the last 3 rows that had the team name in G2 in columns B and C (you could also select those rows with the most recent data for example) and then take the Home GD value if Home = G2 or take the Away GD value if Away = G2. We take the sum of hose three values and then divide by 3. This is the formula I need in cell H2.
For example for Fire this would give us (3-4-1)/3 = -0.667

Adam D. PE · Accepted Answer

The easiest way is to remove the "limit 3" from the QUERY function like this:
=AVERAGE(QUERY(VSTACK(CHOOSECOLS(A:E,1,2,4), CHOOSECOLS(A:E,1,3,5)), "select Col3 where Col2 = '"&G2&"' order by Col1 desc limit 3",0))
=AVERAGE(QUERY(VSTACK(CHOOSECOLS(A:E,1,2,4), CHOOSECOLS(A:E,1,3,5)), "select Col3 where Col2 = '"&G2&"' order by Col1 desc",0))
I hope this helps.

Adam D. PE · Accepted Answer

Hi Popov Siemens,
 
Thank you for the sample file!  I have placed a possible solution for you in the sheet/tab called Help, which I have added to your file.
I hope this helps!
 
Adam
Diamond PE Docs volunteer
Silver PE Classroom volunteer
Cert. Educator 1 & 2
Cert. Trainer
(not a Google employee)
 
When you've received a response that answers your question, please observe these forum courtesies:
 • If you had a Sheets problem, leave your demo sheet shared as View Only as part of this solution's archive,
 • Click Recommend on the post that best addressed your question, and
 • Post again soon!