Rolling/moving average for mixed dat in excel

[gko_87]

Hello every one! I am trying to develop a model for simulation of football statistics in excel and I will really appreciate all the help I can get here.

I am attaching a spreadsheet named "Rolling Averages" with the English Premier League results to the 7th Round. There is a table in which the average goals for, the average goals conceded, the team form and team form summary need to be captured.

1. The average goals for and the averages goals conceded should be based on the last five matches played (home and away combined to make last 5 matches)

2. The team form should also be based on the last 5 matches (home and away matches to make last 5).

3. The team form summary is the last part of the table and the formula used here should be in such a way that the section self updates every time new matches are played and results added

4. I need an excel formula that will perform the above commands.

4. The average goals for, averages goals conceded, team form and team summaries should self update everytime new matches are played and new data added to the workbook.

5. The expected results for one of the teams (Arsenal) are also included as a guide.

5. Any modifications to the worksheet to make all the above possible are welcome.

I will appreciate all the help I can get.




Rolling Averages.xlsx

[AliGW]

Has this been set as a homework assignment?

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

[gko_87]

Hello AliGW! No its it's not a homework assignment. Its a model for simulation of football statics that I am trying come up with. Its not course work but a passion that I developed and decided to pursue separately. Please, do not interpret wrongly. It is honestly a personal project that I decided to seek help with.

Thank you though for the warning.

[AliGW]

OK, so you say that this is a project, but I don't see any attempt at formulae in your sheet, nor is there any expected outcome data in the table top right. If you want someone to create all the formulae for you, then you need to provide data to work with.

[JohnTopley]

You have many of the answers (formulae) in the file I posted in reply to your last thread so any reply here will only be "pointers".

What is the definition of "form"?

[gko_87]

The expected results are below the table (computed manually) not using formulae

[gko_87]

MEANING OF FORM:

This simply means whether a team has won, lost or drawn in a game.

[gko_87]

The answers in the earlier thread used an equal number of matches from home and away fixtures such that if you use 2 as the interval, 2 matches from home and two matches from away are used which equal to four.

In this new situation, the interval should be a sum of the last home and away matches.

The expected results are below the table

[JohnTopley]

You have the formulae for the AVERAGE calculations in the last file I posted.

For FORM SUMMARY, if you simply want to count matches (to date) Won , Lost or Drawn look at COUNTIFS function.

For FORM over last 5 matches, this a variant of the AVERAGE formula already provided: look at AVERAGE as SUM divide by COUNT, so adapting the SUMPRODUCT formulae should provide the required result.

[gko_87]

AliGW, I have a workbook with more than ten football leagues. The part you see is just an extract of the workbook, the only missing part for the model to be complete.

[AliGW]

So what have you tried? Surely you can adapt what John kindly gave you in the other thread to suit the new situation, can't you, or at least have a go and tell us what you have tried? I am sorry to say but, having just read through the other thread and your requests in this one, you don't seem to be making much of an effort here yourself: you seem to want someone else to do it all for you.

[gko_87]

I have a workbook that computes table team positions, matches played, goals scored, goals scored against, points, etc. The part posted above is just an extract. Its a three four month project so you can imagine how much data I have gathered. I turn to the forum when I get stuck. I love excel and I have recently discovered its analysis capabilities and Excel Forum has the best persons to turn to.

[JohnTopley]

You might want to consider adding a column for "Home Team", indicating whether the result was "W","L" or "D"; this will help with some of the formulae you need for the other results.

Post what you think such a formula might be: as per Ali's post, at least try some formulae yourself.

[JohnTopley]

Re the TEAM FORM: do want order to "oldest to newest" across columns R to V?

And is "TEAM FORM SUMMARY" matches to date: if so, YOU try COUNTIFS.

[JohnTopley]

Testing reply works OK.

[gko_87]

For ease of understanding, I am attaching the spreadsheet again with the matches to be considered highlighted in red. John, I tried your function/formula but it is giving 1.60 instead of 2.60. The desired results are the ones in the spreadsheet.

With the formula:

=SUMPRODUCT(($D$6:$E$3000)*($B$6:$B$3000>=LARGE(--($C$6:$C$3000=$J8)*($B$6:$B$3000),$M$2))*($C$6:$C$3000=$J8))/$M$2

I am able to get the desired average goals for but it does not self-update when new data is added or data is deleted, and it seems to be counting empty cells.

I will be glad to get formula that fills the other columns in the table and self-updates.

Rolling Averages.xlsx

[gko_87]

Goals conceded are goals scored against.

[JohnTopley]

There are only 3 home games for Arsenal with goals of 3,2 3 =8 divided by 5 =1.6

Arsenal Away games have scores of 3,4 and 1 = 8

Add Home and Away to get 13 divided by 5 = 2.6

Your solution is average of HOME and AWAY: my solution was HOME only

You have changed you file which originally had H, M and OVRL!!!! now only OVRL.

So you solution is correct.

[gko_87]

It is what I wanted. Average for home and away is what I want. Sorry I did not say that before.

[JohnTopley]

You need to change LARGE to look at BOTH home and AWAY Dates

=LARGE(--(($C$6:$C$3000=$J8)+($F$6:$F$3000=$J8))*($B$6:$B$3000)

You are only looking at home dates.

[gko_87]

John, can you help modify the formula so that it self updates when new data is added or data deleted. Empty cells should also be ignored but zeros counted.

I have been struggling with this since yesterday.

AND FOR THE TEAM FORMS: can be countif be used

[gko_87]

That formula does not look at the scores which are the key point of reference.

[gko_87]

it only looks at the home column, the away column and the date column

[gko_87]

It does not give the result 2.60

[gko_87]

and the last five home and away matches are not factored in.

[gko_87]

When I try: =LARGE(--(($C$6:$C$3000=$J8)+($F$6:$F$3000=$J8))*($B$6:$B$3000)*($D$6:$E$3000),$M2) i get the "VALUE" error but when i enter the above function as an array i get 127785.00

[gko_87]

what am i missing? the result should be 2.60

[JohnTopley]

Try

=(SUMPRODUCT(($D$6:$D$70)*($B$6:$B$70>=LARGE(--(($C$6:$C$70=$J8)+($F$6:$F$70=$J8))*($B$6:$B$70),$M$2))*(($C$6:$C$70=$J8)))+SUMPRODUCT(($E$6:$E$70)*($B$6:$B$70>=LARGE(--(($C$6:$C$70=$J8)+($F$6:$F$70=$J8))*($B$6:$B$70),$M$2))*(($F$6:$F$70=$J8))))/$M$2

gives 2.6 for Arsenal

You have to separate the HOME and AWAY games.

[gko_87]

How can I modify the IF functions in the spreadsheet to ignore empty cells. when 2 cell facing each other are both empty, it gives D (Draw) instead og ignoring them


Rolling Averages.xlsx

[JohnTopley]

in G

=IF(D5="","",IF(D5>E5,"W",IF(D5=E5,"D",IF(D5<E5,"L"," "))))

in H


=IF(E6="","",IF(E5>D5,"W",IF(E5=D5,"D",IF(E5<D5,"L",""))))

[gko_87]

Thank you so much, that was very helpful. How about the goals conceded? And how would I pull up the match outcomes for Arsenal to fill the cells O8:S8 in last spreadsheet posted?

[gko_87]

Expected results are just below the table.

[gko_87]

Will really appreciate. It will be of great hep and time saving if the functions will autofill downwards also.

[JohnTopley]

TEAM FORM

in R8

=IFERROR(INDEX($G$6:$G$385,LARGE(IF((($C6=$C$6:$C$385)+($C6=$F$6:$F$385)),ROW($A$6:$G$385)-ROW($A$6)+1,""),$M$2-COLUMNS($A:A)+1)),"")

Enter with Ctrl+Shift+Enter

Drag across to V and then down

R8 is OLDEST result, V8 is Latest result

TEAM format SUMMARY (ALL results to date)

WINS

=COUNTIFS($C$6:$C$385,$C6,$G$6:$G$385,"W")+COUNTIFS($F$6:$F$385,$C6,$H$6:$H$385,"W")

Change "W" to "D" and "L" for "Draws" and "Loss"

[JohnTopley]

Correction:

TEAM FORM is wrong formula: need to re-Think!!

[gko_87]

I just tried and its giving me an empty cell? are you able to attach the sheet so i can see, please?

Have you been able to work on the goals conceded?

[gko_87]

The formula for summary is working but it is basing on all the matches played by the teams. I needed only the latest 5 (home and away) just like the one for average goals conceded as highlighted in the spreadsheet.

[JohnTopley]

Sorry but my brain is hurting so I am going to take a break until tomorrow.

[gko_87]

Goals conceded, team form and team form summary should also be based on the last five as highlighted. Team summary is basically asking the question: Out of the five matches highlighted, how many are wins, draws or losses for the team in question.

[gko_87]

Ok thanks John. I will also take a break. Thank you so much for you time I really appreciate. See you tomorrow.

[JohnTopley]

For TEAM FORM (LAST 5):


I used "helper" columns to return the rows of the last five matches for each team.

=IFERROR(INDEX(ROW($A$6:$A$385),LARGE(IF((($K8=$C$6:$C$385)+($K8=$F$6:$F$385)),ROW($A$6:$A$385)-ROW($A$6)-4,""),$N$2-COLUMNS($A:A)+1)),"")

Enter with Ctrl+Shift+Enter

copy across and down

in O2

=IF(INDEX($C$6:$C$385,X8)=$K8,INDEX($G$6:$G$385,X8),INDEX($H$6:$H$385,X8))

copy across and down

If TEAM FORM SUMMARY is count of last 5 games, then change to

=COUNTIF($O8:$S8,"W")

=COUNTIF($O8:$S8,"D")

=COUNTIF($O8:$S8,"L")

in T, U, V