1

I am looking for a formula to generate a moving average of the last two weeks OR the last 10 data points (whichever produces more data points) conditional upon the presence of data in two other columns. Example.

I want to calculate: The average of column $K for the past two weeks (from today's date) OR the past ten data points (whichever is a larger data set) when column $G=HenkkyG and column $U=LAN.

Effectively I want Player HenkkyG's average over the past two weeks or 10 games (data points).

I am currently using this formula for overall average:

=IFERROR(AVERAGEIF($G:$G,AF2,$K:$K))

where AF2=player name I am drawing data for.

Improve this question
1

This can be done with a few filter commands.

To filter by columns G and U: 

=filter(B2:K, (G2:G = "HenkkyG") * (U2:U = "LAN"))

(Here, multiplication is logical, meaning AND).

To filter the scores by "either within 14 days or among the last 10", the condition would be: 

=filter(K2:K, (B2:B >= today()-14) + (rank(B2:B, B2:B, false) <= 10))

Here + is logical OR, and the rank is in descending order, picking the 10 largest entries from the date column.

It remains to combine these. In the interest of maintainability, it may be best to do things separately (perhaps on another sheet): apply the first filter, and then use the second on its output. But it's possible to do everything in one formula, it just looks scary: the first filter is applied to each column appearing in the second filter.

=filter(filter(K2:K, (G2:G = "HenkkyG") * (U2:U = "LAN")), (filter(B2:B, (G2:G = "HenkkyG") * (U2:U = "LAN")) >= today()-14) + (rank(filter(B2:B, (G2:G = "HenkkyG") * (U2:U = "LAN")), filter(B2:B, (G2:G = "HenkkyG") * (U2:U = "LAN")), false) <= 10))

This is not the kind of formulas that I would want to deal with in a spreadsheet inherited from someone else.

Improve this answer
10
  • I thought this might be the case. So for the sake of keeping the sheet efficient (I am dealing with thousands of columns of data) it would be best to have cells for each filter formula and then the formula to work with those in the separate (stats/formula sheet) correct? – Bigmantree Mar 19 '16 at 23:13
  • I think this may be better. After the first filter (which is a straightforward one) the data size will not be quite as large. – user79865 Mar 19 '16 at 23:19
  • Great, I will plug these in and get back to you. Thanks a ton for the help. I guess I was trying to make it too complicated initially by figuring out how to put it all in one formula which isn't how I'd want it anyway! – Bigmantree Mar 19 '16 at 23:34
  • Right so all these formulas work perfectly, however I am running into a slight problem with the first formula. What I am trying to do to cut down on manual entry is to use column X as a list of the players who I want this equation to pull data for. If I make the equation nonarray and drag it through the rows I run into overlap errors and it won't show the data. If I make it an array it seems to leave everything blank. For reference the two formulas I am using are: =ArrayFormula(filter(B2:K,(G2:G=X2:X)*(V2:V="Online"))) and =(filter(B2:K,(G2:G=X2)*(V2:V="Online"))) – Bigmantree Mar 19 '16 at 23:45
  • So it would be like G2:G = X3 or something, I suppose. – user79865 Mar 19 '16 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.