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
Bookmarks