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

 

[ukmikeb]

Hi

Try for the For goals :-

=SUMPRODUCT(--($A$11:$A$16="AFC Wimbledon")+--($B$11:$B$16="AFC Wimbledon"),--(INDEX(($D$11:$D$16,$E$11:$E$16),,,IF($G$11:$G$16="H",1,2))))

and for the Against goals :-

=SUMPRODUCT(--($A$11:$A$16="AFC Wimbledon")+--($B$11:$B$16="AFC Wimbledon"),--(INDEX(($D$11:$D$16,$E$11:$E$16),,,IF($G$11:$G$16="A",1,2))))

both entered with Control-Shift-Enter and change the ranges to suit.

ps Sorry I couldn't get the true ranges because I couldn't download the file.

hth

 

[ukmikeb]

Hi

Assuming the Team names are in F and G then the above becomes -
Goals For :-

=SUMPRODUCT(--($F$11:$F$16="AFC Wimbledon")+--($G$11:$G$16="AFC Wimbledon"),--(INDEX(($I$11:$I$16,$J$11:$J$16),,,IF($L$11:$L$16="H",1,2))))

Goals Against :-

=SUMPRODUCT(--($F$11:$F$16="AFC Wimbledon")+--($G$11:$G$16="AFC Wimbledon"),--(INDEX(($I$11:$I$16,$J$11:$J$16),,,IF($L$11:$L$16="A",1,2))))

Both entered with Control-Shift-Enter.

hth

 

[Andrew Poulsom]

Why not:

=SUMIF(F8:F13,"AFC Wimbledon",J8:J13)+SUMIF(G8:G13,"AFC Wimbledon",K8:K13)

=SUMIF(F8:F13,"AFC Wimbledon",K8:K13)+SUMIF(G8:G13,"AFC Wimbledon",J8:J13)

 

[AlphaQuail]

Hi Mike,

Thanks for coming back to me on this one. You'll have to forgive my simple-mindedness here. The above don't seem to give the desired result however. I think the formulas provided use the results column (for home or away) in determining whether Wimbledon were at home or not whereas that colulm is the actual result of the game (home win, away win, draw)

If you can get into the file I've attached a snapshot of the file in the hopes this might help? Essentially I need to count how many goals have been scored by wimbledon over their last 6 appearances and how many goals they've had scored against them. I'll then apply the same logic to the away team in the game I'm analysing (which in this example is Accrington.

Again, many thanks in advance.



Lee

 

[Andrew Poulsom]

Did you try the formulas I posted?

 

[AlphaQuail]

hi Andrew,

I didn't see you original post until just now. I've entered those in and it seems to deliver with one slight snag. i only want to sum the last 6/most recent 6 times Wimbledon appear, which is fine for a fixed data range, but I want to be able to copy this formula down. Therefore in a longer data set, I won't be summing the goals scored beyword 6 appearances ago.

Thanks

lee

 

[Andrew Poulsom]

Does each row in your lists in columns F:G always contain the team you want to analyse?

 

[AlphaQuail]

If it helps I had one solution from a previous thread which for home team goals for was:

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

This summed the most recent goals as desired but treat home appearances and away appearances separately. I need this to be combined

Thanks

Lee

 

[Andrew Poulsom]

Is this what you want?

=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")