Calculating winning/drawing/losing runs & goal scoring runs

Greetings Good People,

I am attaching two workbooks with the same data (2017/2018 premier league results to the 11th round, but the dates are from 2016). Below is my description of my terminologies:

H = only home matches considered in calculations.

A = only away matches considered in calculations

H/A = both home and away matches considered in calculations

Winning Streak – number of most consecutive matches won by a team (only won games are considered)

Winless Streak – number of most consecutive matches played by a team without winning (drawn and lost matches are considered)

Drawing streak – number of most consecutive matches drawn by a team (only matches drawn are considered)

Drawless streak – number of most consecutive matches played by a team without drawing (only won and lost matches considered)

Losing streak – number of most consecutive matches lost by a team (only lost matches considered)

Lossless Streak – number of most consecutive matches played by a team without losing (only won and drawn matches are considered)

Scoring streak – number of most consecutive matches played by a team without failure to score (only consider matches where a team scores at least a goal)

Goalless streak – number of most consecutive matches played by a team without scoring a goal (only consider matches where a team does not score any goal)

Longest run without conceding – number of most consecutive matches played by a team without a goal being scored by the opponent team

Longest run with goal conceded – number of most consecutive matches played by a team where the opponent team scores at least a goal

REQUIRED:

I would like to be able to compute the above in the two workbooks, but the workbook labeled “Rolling calculations” will consider the number of matches tied to cell N1.

Note: In the “Rolling Calculations Workbook”, for H calculations, consider the last 4 home matches; for A calculations, consider the latest 4 away matches. However, the matches to be considered for H/A calculations for Manchester united are highlighted in yellow: [4 is the value in cell N1].

In the all matches workbook, For H calculations, consider all home matches; for the A calculations, consider all away matches; and for H/A calculations, consider all matches played at home and away i.e. sum of home and away matches.

There is a drop down in cell B2. Selecting a team in this drop down highlights all matches played by that team in read.

Thanks in advance.

Rolling Calcultions.xlsx

ALL MATCHES.xlsx

If You can attach sheet with desired result, it would be easy to understand.

Hopefully I'm understanding your request correctly. I first changed the formula in M6 to the following:

=IF(Teams!$B3="","",TRIM(Teams!B3))

...then filled down. Some of your team names on the 'teams' sheet have trailing spaces; TRIM removes those spaces from consideration. For the rest of your table, my formulas followed the same basic structure. All formulas should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter) and filled down. I used the following in N6 and filled down:

=SUMPRODUCT(N(IF((H$6:H$150="W")*($D$6:$D$150=$M6),ROW($D$6:$D$150),-1)>MAX(IF((H$6:H$150<>"W")*($D$6:$D$150=$M6),ROW($D$6:$D$150)))))

This formula finds the row number of all of the team's home wins and compares that to the MAX row number of the team's last non-win. A slight modification gets us the away formula in O6:

=SUMPRODUCT(N(IF((I$6:I$150="W")*($G$6:$G$150=$M6),ROW($G$6:$G$150),-1)>MAX(IF((I$6:I$150<>"W")*($G$6:$G$150=$M6),ROW($G$6:$G$150)))))

And then combine the two possibilities for the H/A in P6

=SUMPRODUCT(N(IF(((H$6:H$150="W")*($D$6:$D$150=$M6))+((I$6:$I$150="W")*($G$6:$G$150=$M6))>0,ROW($D$6:$D$150),-1)>MAX(IF(((H$6:H$150<>"W")*($D$6:$D$150=$M6))+((I$6:$I$150<>"W")*($G$6:$G$150=$M6))>0,ROW($D$6:$D$150)))))

The rest of the formulas for your other columns are simply modifications on this structure. Change "W" to "L" for losses. Change = to <> and vice versa for winless streak. Change H6:H150="W" to E6:E150>0 for scoring streak, etc. The results that I spot checked in the attachment appeared to be calculating correctly. If you want to cover a larger range, change all of the $150s in the formulas to $1500s, which will allow you to add more score results.

If you want to shorten the results to only consider the last "X" number of matches (4 in your example), just wrap each formula in a MAX function. For example:

=MAX(formula, $N$1)

Hopefully this does the trick? Check out the attachment to see if it helps:

Thank you so much CAntosh for your reply. Just went through some of the answers generated by the formulas and realized the following:

Newcastle: most consecutive home games won = 2 games (see game 23 and game 45 where Newcastle wins both games which are consecutive), but the formula in cell N7 returns a zero instead of 2.

Brighton: most consecutive home games won = 2 games (see game 35and game 59 where Brighton wins both games which are consecutive), but the formula in cell N8 returns a zero instead of two.

Manchester United Away: this team played the first three consecutive matches away without failing to score (check matches 11, 37 and 54 where man united scores at least a goal. The formula in AG6 returns a zero instead of 3.

Note: if the most consecutive matches won by a team =2 matches, then the formula should return 2 under winning streak..

And if the most consecutive matches in which a team has scored a goal = 3 matches, then the formula should return 3 under scoring streak.

Ah, I misunderstood the task. My results show ONGOING streaks, so they show each team's current streak for the given category. Apologies. I can look at what you're requesting, but probably not until next week. Hopefully somebody else will jump in before then.

These streaks will be based on all matches played as teams play and should therefore self update as more games are played.

But the "Rolling Calculations" workbook is the one in which I will need calculations done on a specified number of matches.

Here is the UPDATED file:

Copy of FINAL TEMPLATE.xlsx

Updated File with actual positioning of data with extra fields deleted.

Copy of FINAL TEMPLATE.xlsx

To all who may assist with this thread:

Use attachment in post #11 as this shows the actual positioning of data in my actual workbook. The attachments by John Topley in posts #8 and post #9 give desired results using VBA in the fields already worked on.

Let me go through the file.

John, this last attachment differs a lot from your first attachment in post #7. It has a lot of 2's.