Rolling calculations based on interval

In the workbook attached, the last 5 matches played by Watford are shaded in red. Out of these 5, three were home matches while two were away matches.
Out of these 5 matches, what would be the formula that would calculate matches won at home, matches won away, matches drawn away, matches drawn away, matches lost at home, matches lost away, total goals scored at home, total goals scored away, total goals conceded at home and total goals conceded away. The formula should self-update when more data is added to the workbook.

Expected results for Watford are below the calculations table.
Note: The interval (i.e. last 5 matches) should be tied to cell M1 so that it can be changed at any time without altering the formula. Only the last 5 matches are to be considered (shaded in red)

Attachment 513054

Hi Onditi,

As you have found, the format of your data with Home and Away teams is a problem. A team could be in the Home column or the Away column, making it very hard to do formulas on them.

I'd suggest you look at the attached file where I've entered your data in a different way on Sheet2. Entering results in this manner will allow you to do Pivot Tables and get the results you want. I need to add a column of goals scored against and then work from there. See what I've done to see if you can get your answers from this new format of the information.

Home and Away better Table.xlsx

Thanks Marvin, for your response. But there is nothing wrong with the format as other calculations are working fine. I believe it is just a matter of referencing. I believe with the count function this can be achieved by counting the number of wins, draws and losses. For the goals scored the sumif function applies but I don't know how to restrict my formula to only the latest five matches.

M6=sumproduct(($c$6:$c$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($g$6:$g$385="w"))
n6=sumproduct(($f$6:$f$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($h$6:$h$385="w"))
o6=sumproduct(($c$6:$c$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($g$6:$g$385="d"))
p6=sumproduct(($f$6:$f$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($h$6:$h$385="d"))
q6=sumproduct(($c$6:$c$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($g$6:$g$385="l"))
r6=sumproduct(($f$6:$f$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($h$6:$h$385="l"))
s6=sumproduct(($c$6:$c$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($d$6:$d$385))
t6=sumproduct(($f$6:$f$385=$l6)*(row($c$6:$c$385)>=max(row($c$6),large(index((($c$6:$c$385=$l6)+($f$6:$f$385=$l6))*row($f$6:$f$385),0),$m$1)))*($e$6:$e$385))
try this and copy towards down
see the attached file

Thank you nflsales. Your formula worked magic.

In the workbook attached, matches played by Watford both at home and away.

In the last 5 matches played at home, Watford has won 2, drawn in 1, lost 2, scored 8 goals and conceded 8 goals.

In the last 5 away matches, Watford has won 2, drawn in 2, lost 1, scored 6 goals and conceded 6 goals.

In the last 5 Home/Away matches, won 2, drawn in 2, scored 4 goals and conceded 4 goals.

What would be the formula that would calculate these values? The formula should self-update when more results are captured.

Notes:
i. For home calculations, consider only home matches (5 home matches)
ii. For away calculations, consider only away matches (5 away matches)
iii. For Home/Away (H/A), consider only the latest 5 home and away matches by date.
iv. The interval should be tied to cell M1

Attachment 513100

You said that Nflsales' formula "worked magic", but I don't see it in your new workbook. However the request is ostensibly the same - please explain (a) in what way the formula you previously accepted as working now isn't functioning correctly and in what way and (b) what you have tried to change in it to make it work.

AliGW, the workbook and the question I posted earlier was wrong. The initial and current questions are different. I realized the mistake while testing the formulas.

Yes, but it is still the same issue. Keep the discussion here, please, and explain in what way this query differes slightly from your original.

The range of data to be taken into consideration differs depending on the fields of calculations. It is explained before the two attachments?

Kindly check the info related to each attachment for the difference.

I am sorry, but I can see no real difference between your latest sheet and the one you posted before. If all that you are wanting changed is the range of data, then surely you can adapt the formulae you were given before to take the new range into consideration?

Try .. based on Siva's formulae in Post #4

For WATFORD:

M17

=SUMPRODUCT(($C$6:$C$385=$L17)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L17))*ROW($C$6:$C$385),0),$M$1)))*($G$6:$G$385="w"))

N17

=SUMPRODUCT(($F$6:$F$385=$L17)*(ROW($F$6:$F$385)>=MAX(ROW($C$6),LARGE(INDEX((($F$6:$F$385=$L17))*ROW($F$6:$F$385),0),$M$1)))*($H$6:$H$385="w"))

O17

=SUMPRODUCT(($C$6:$C$385=$L17)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L17)+($F$6:$F$385=$L17))*ROW($F$6:$F$385),0),$M$1)))*(($G$6:$G$385="w")+($H$6:$H$385="w")))

For Away and D just change "W" to "A" and "L"

V17

=SUMPRODUCT(($C$6:$C$385=$L17)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L17))*ROW($F$6:$F$385),0),$M$1)))*($D$6:$D$385))

W17

=SUMPRODUCT(($F$6:$F$385=$L17)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($F$6:$F$385=$L17))*ROW($F$6:$F$385),0),$M$1)))*($E$6:$E$385))

in X17


=SUMPRODUCT(($C$6:$C$385=$L17)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L17)+($F$6:$F$385=$L17))*ROW($F$6:$F$385),0),$M$1)))*($D$6:$D$385))+SUMPRODUCT(($F$6:$F$385=$L17)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L17)+($F$6:$F$385=$L17))*ROW($F$6:$F$385),0),$M$1)))*($E$6:$E$385))

for Y, Z,AA reverse the D and E ranges

My results are in Row 31

Thank you John, this is the solution I needed. God Bless.

HERE IT IS:

When home matches won, drawn and lost are summed up, the total should be 5 unless the team in question has played less than 5 matches on home ground e.g. Liverpool which has played 4 matches at home. When away matches won, drawn and lost are also summed up, the total should also be five unless the team has played less than 5 matches away from home e.g. Burnley.

But when Home/Away matches won, drawn or lost are summed up, the total should strictly be 5 because we are considering the latest five matches. In this case, the venue is not important since we are considering the 5 matches not by venue of play. So the total for H/A under games played in the calculations table should be 5.

Note: Matches to consider for H/A computations for Watford are highlighted.

Attachment 513255

I was thinking that maybe the date column should also be considered?

As you seem to want further help, I've marked the thread as unsolved.

Thank you AliGW.

...then add all dates to your file (whether any solution requires them or not).

Checking your files, the results for Watford are the same with only the added columns showing that we consider five games which is value in M1

John, sorry to trouble you. would you be kind to help feed the formula in the file using another approach apart from the one already used? I am still struggling to learn the SUMPRODUCT functions and Moving means and other Rolling/Moving calculations.

"When home matches won, drawn and lost are summed up, the total should be 5 unless the team in question has played less than 5 matches on home ground e.g. Liverpool which has played 4 matches at home. When away matches won, drawn and lost are also summed up, the total should also be five unless the team has played less than 5 matches away from home e.g. Burnley.

But when Home/Away matches won, drawn or lost are summed up, the total should strictly be 5 because we are considering the latest five matches. In this case, the venue is not important since we are considering the 5 matches not by venue of play. So the total for H/A under games played in the calculations table should be 5."

Note: Matches to consider for H/A computations for Watford are highlighted.

Attachment 513266


Note: Kindly refer to my last attachment. We used Watford as a sample but the formula should apply to all teams. The totals in column "O" under H/A should be five:

Onditi - your data is incomplete. You need to include ALL DATES, as requested by John above.

Here is the file with all dates included. John, sorry I had missed the date part in your last post. AliGW, thanks for the correction.

Attachment 513270

For H/A

=SUMPRODUCT(($C$6:$C$385=$L6)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L6)+($F$6:$F$385=$L6))*ROW($F$6:$F$385),0),$P$1)))*(($G$6:$G$385="w")))+SUMPRODUCT(($F$6:$F$385=$L6)*(ROW($C$6:$C$385)>=MAX(ROW($C$6),LARGE(INDEX((($C$6:$C$385=$L6)+($F$6:$F$385=$L6))*ROW($F$6:$F$385),0),$P$1)))*(($H$6:$H$385="w")))

Change W to D and L

Thank you John.

Just wondering if the OP is interested in this workbook I threw together.

It's not complete, properly formatted, divisions/conferences aren't set up properly yet, and so on.

If you are interested, let me know and I can do all the formatting according to how your league is set up.

Is this the league you're following?
http://www.bbc.com/sport/football/premier-league/table