Football Data - Analysing recent team form (I have half of the solution in SUMPRODUCT)

[AlphaQuail]

https://drive.google.com/file/d/0B0csbmxO50sARnlNd203a1MyMzg/edit?usp=sharing

Above is a link to the Google Drive doc for this post

I'm trying to sum together goals scored for and against for a particular team in the previous X number of games played. The doc shows home teams in column F and away in column G. The formula I have already can only sum the previous X number of games goals (6 in my working example) for home appearances and away appearances separately. I need to tweak the formula I have to account both column F & G together.

In this example, I'm essentially looking to determine how goals AFC Wimbledon have scored over the past 6 times they have appeared (it would be 6, 4 at home and 2 away) and the number of goals score against them (it would be 7 here, 3 home and 4 away) I have highlighted these for clarity.

I've included the formulas I've been working with to date which is a combination of another post response and help from a colleague. I know this isn't far off the end product with my attempts to nest a FIND function somewhere in this looking at a CONCAT of F&G not delivering a sound result

Can any one help?

Thanks

Lee

 

[AlphaQuail]

Andrew, yes it will. Essentially I'll be updating F & G with new data each week to look at. Therefore, each time a game is played, I'll drop the oldest game from my analysis. I do however want to apply this logic to some historic data, hence why I need to copy this down over many rows

 

[AlphaQuail]

Almost but I need to include goals scored by wimbledon when they have played away too, so when wimbledon appear in G as well as F. Goals SUM of both J & K. Does that make sense?

Lee

 

[Andrew Poulsom]

Why can't you use 2 formulas, one for J and the other for K?

 

[AlphaQuail]

Mainly because over the say, last 6 games, a football team plays, they could play 4 on home turf and 2 away from home or 3 and 3 or what ever. I therefore, need to review the goals scored for/against when the team appears in either column F or G collectively, not separately. This will give me the total number of goals scored by that team over the last 6 appearances whether or not they played at home or away. The current model I think just looks at last 6 home games (which doesn't include the games in between where they may have scored away from home).

The website owner where I get my data from has suggested a much more longer winded copy and paste job where I copy paste the away games under neath the home data and then apply a similar formula to what we have, which I'm trying to avoid as it's a bit labour intensive.

 

[Andrew Poulsom]

If the formula I gave you works for home games, for away games use K$3:K13 instead of J$3:J13.

 

[AlphaQuail]

Oh right, I think I see what you mean. Thanks for this Andrew... I appreciate your help. I'll give that a try

 

[AlphaQuail]

Hi Andrew,

Last time promise... what youve gave me works!! I can get the number of goals scored by the home team at home and the goals score against the same team when they played away. I need goals score against the home team at home and goals scored when they played away.
I just need to manipulate the formula again to get the two remaining columns for Wimbledon. Either that or a formula that adds all goals over wimbledons last 6 appearances, regardless of whats in cell F14 then I can take my orginal value away from this to give me the difference which will then be the figure I seek.

I've colour coded the the numbers I still need to calculate as per below and put in row 15 what these should be?

 

[Andrew Poulsom]

Try:

HGF =IF((COUNTIF(F$3:F13,$F13)+COUNTIF(G$3:G13,$F13))>=$K$1,SUMPRODUCT((ROW(J$3:J13)>=LARGE(((F$3:F13=$F13)+(G$3:G13=$F13))*ROW(J$3:J13),$K$1))*(F$3:F13=$F13)*J$3:J13),"n/a")

HGA =IF((COUNTIF(F$3:F13,$F13)+COUNTIF(G$3:G13,$F13))>=$K$1,SUMPRODUCT((ROW(K$3:K13)>=LARGE(((F$3:F13=$F13)+(G$3:G13=$F13))*ROW(K$3:K13),$K$1))*(G$3:G13=$F13)*K$3:K13),"n/a")

AGF =IF((COUNTIF(F$3:F13,$F13)+COUNTIF(G$3:G13,$F13))>=$K$1,SUMPRODUCT((ROW(J$3:J13)>=LARGE(((F$3:F13=$F13)+(G$3:G13=$F13))*ROW(J$3:J13),$K$1))*(F$3:F13<>$F13)*J$3:J13),"n/a")

AGA =IF((COUNTIF(F$3:F13,$F13)+COUNTIF(G$3:G13,$F13))>=$K$1,SUMPRODUCT((ROW(K$3:K13)>=LARGE(((F$3:F13=$F13)+(G$3:G13=$F13))*ROW(K$3:K13),$K$1))*(G$3:G13<>$F13)*K$3:K13),"n/a")

 

[AlphaQuail]

Wow, I think that's it. The <> is does not equal, of course. Thanks Andrew, that's awesome!!!!

Lee