to sum 6 matches from a list and to count the goal difference.

[andyh42]

HI,

I would like to sum the goals difference by a team for their last 6 matches, both home and away.

The first figure will appear in cell G71 showing the last 6 games goal difference for Bradford and in cell H71 for the last 6 games goals difference for Colchester.

Each match has a Match No.

so the final result would be
Match No. Match Wk. Date Home Tm Away Tm h/gdiff a gdiff home away
70 7 14/9 Bradford Colchester 10 -1 2 2

and then copied for the rest of the matches that season for all teams.

Thank you.

[dflak]

I can get you part of the way there mainly because I can't see where you are ultimately going.

On the League sheet, I converted the range into an Excel Table - it's just easier to work with tables. I added some helper columns.
- Selected =OR([@HomeTeam]=Pivot!$C$1,[@AwayTeam]=Pivot!$C$1) - this is TRUE if the selected team is either the home or away team.
- Goal Diff =IF([@Selected]=TRUE,IF([@HomeTeam]=Pivot!$C$1,[@home]-[@away],[@away]-[@home]),"") - this gets the difference in goals based on whether the team is home or away
- Date Rank =RANK([@Date],[Date]) - I use this to limit the pivot table to the top 6 dates. Although shown on the pivot table, it isn't used in any calculations. It's just there to order the data.

On the pivot sheet Select the team in cell C1 and then refresh the pivot table. This will give you the results of the last 6 games.

I think this pivot table has all the information you need for your analysis. Could you walk me through the calculations based on the information provided.

The part that throws me is "and then copied for the rest of the matches that season for all teams."

[andyh42]

Hi,

Its almost correct apart from I should of used the word previous rather than last. I'm looking at being able to put a match number in a reference cell then the previous six games for that team to be put into the pivot table. At the moment you are showing the last 6 games of the season. I would like to be able to refer to any stage of the season and see the results for the previous six games.

Example:

match no. 71 team name Bradford

then it would bring up the previous six games before match no. 71 for Bradford.

I think this makes sense.

Don't worry about the copying down comment It was referring to if it was a formula.

Regards.





Do worry about the coping it down remark that was if it was a formula.

[johnmpl]

Hi, andyh42! (and dflak)

Check this file. If I had understood, this is what you need. Blessings!

[dflak]

Ok, same logic except that I added another selection for match number. I'll automate the pivot refreshes when you confirm that this is what you want. I will also "clean up" the helper cells and such as well as give some more information on how it works.

Enter a team, refresh pivot. This gives you a valid list of match numbers for that team. Select the minimum match number and refresh the pivot again.

[andyh42]

Hi

Sorry about the length of time but Christmas and illness got in the way. That's ok it does what I need. Thank you.

Regards.

[FlameRetired]

andyh42 out of curiosity to which solution do you refer?

Also, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[andyh42]

Hi Dave

I'm trying to create a table of results using the previous 6 league games goal difference total for the match as a probability model. The total is the home goal difference (HGD) minus the away goal difference (AGD)
Example:
Home away HGD AGD Total Result
Man Utd v Liverpool +2 +1 +1 2 1 Home win

This will then show on the row for goal difference total +1 as a home win.
GD total Home win Draw Away Win Home win % Draw Away
+1 1 0 0 100% 0% 0%

At present I'm importing the previous seasons results (league1 on the spreadsheet) and using a website to input the home and away goal difference from week 7 onwards by hand, which is fun, this then allows me to build up the goal difference total table each season. The table goes from -30 to +30 and uses sumproduct to total the results.
Example:
GD total Home win Draw Away Win Home win % Draw Away
-1 7 3 5 47% 20% 33%
0 9 4 4
+1 10 2 2

This is then added to previous seasons results and the table builds in size.

Now I would like to expand the leagues available too me. I was hoping too be able to import the results and have a formula too work out the goal difference total from week 7 for me as all the results are known.

The pivot table is a help to me because it was quicker than using the website and this is my hobby so there is no time pressure. If you could just finish it off for me so it updates that would be great.

Hope that satisfies the curiosity.

Regards..

[andyh42]

HI,

Sorry for the late reply. I totally missed this post This exactly what I was looking for. Thank you. Regards