calculate the goal scored and conceded in the last 6 football Matches

**mansh34** · 06-11-2022, 04:32 PM

Please friends how can I get the formula that will calculate the goal scored and conceded in the last 6 football Matches at home, away, and overall in an excel spreadsheet,I can provide an attachment if needed. I will be very happy to get an absolute answer and help thanks in advance.. from mansh

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Please Help" doesn't tell us anything about your question. I have updated it for you this time because you are a new member. --6StringJazzer

**Pete_UK** · 06-11-2022, 08:40 PM

We can't very easily give you a formula if we don't know anything about how your data is laid out, so it would be a good idea to attach a sample workbook. Follow the instructions in the yellow banner at the top of the screen to do this.

Pete

**mansh34** · 06-12-2022, 12:07 AM

Pete_UK Thank you for your reply and also to the moderator for helping me out also, I have uploaded an attachment to the workbook

**JohnTopley** · 06-12-2022, 02:38 PM

A sample including at LEAST 6 matches for the selected team will help.

**mansh34** · 06-12-2022, 03:05 PM

I was thinking that if a certain team have not played up to 6 matches then, it should display the played matches, but if they have played up to 6 then, it should show last 6 matches…thanks in advance

**Pete_UK** · 06-12-2022, 05:22 PM

I remember doing something like this years ago - I'll look tomorrow and see if I can find the references to it.

Pete

**mansh34** · 06-12-2022, 05:25 PM

Ok thank you friend, I will patiently wait for your help..

**Pete_UK** · 06-13-2022, 05:43 AM

I didn't find the file I was looking for - looked through the file names for 1500+ files that I have submitted here, but didn't find any that seemed suitable (there were many called sample or book1 !!)

However, I did notice when I re-opened your file that it was very similar in terms of headings with the file in this thread:

https://www.excelforum.com/excel-pro...ell-value.html

Did you base your file on that one?

Pete

**mansh34** · 06-13-2022, 06:03 AM

Thanks for your response, yes the headings are the same. The data was downloaded from the football data site, I was trying to send the site along but were not allowed to do so

**mansh34** · 06-13-2022, 06:10 AM

please help, if I could only get the formula for getting the last nth matches played, according to my table layout, then I guess I could continue from there.

**Pete_UK** · 06-13-2022, 06:21 AM

I think this is the thread I was thinking about (I thought it was years ago, though):

https://www.excelforum.com/excel-for...or-league.html

You might also want to look at this thread:

https://www.excelforum.com/excel-for...ue-arrows.html

Hope this helps.

Pete

**mansh34** · 06-13-2022, 06:25 AM

Ok, Thank you lemme go through them, I will get back to you soon.

**mansh34** · 06-13-2022, 06:47 AM

I only see win, draw, and loss from the files you sent me the links. But I mean I wanted the goals scored and conceded from the last nth matches played...

**mansh34** · 06-15-2022, 01:43 AM

Hello friends, can anybody help me out on this challenge am having, I can't still figure out goals scored and conceded in the last nth matches played.anybody with help yet?

**JohnTopley** · 06-15-2022, 06:32 AM

Helper column X

=IF(OR($D2=$Y$7,$E2=$Y$7),IF(MAX($X$1:X1)>6,"",COUNTIF($D$2:E2,$Y$7)),"")

in Z7

=MAX($X$2:$X$20)

in AA7

=COUNTIFS($X$2:$X$20,"<>",$D$2:$D$20,$Y$7,$H$2:$H$20,"H")+COUNTIFS($X$2:$X$20,"<>",$E$2:$E$20,$Y$7,$H$2:$H$20,"A")

in AB7

=COUNTIFS($X$2:$X$20,"<>",$D$2:$D$20,$Y$7,$H$2:$H$20,"D")+COUNTIFS($X$2:$X$20,"<>",$E$2:$E$20,$Y$7,$H$2:$H$20,"D")

in AC7

=COUNTIFS($X$2:$X$20,"<>",$D$2:$D$20,$Y$7,$H$2:$H$20,"A")+COUNTIFS($X$2:$X$20,"<>",$E$2:$E$20,$Y$7,$H$2:$H$20,"H")

in AD7

=COUNTIFS($X$2:$X$20,"<>",$D$2:$D$20,$Y$7,$H$2:$H$20,"A")+COUNTIFS($X$2:$X$20,"<>",$E$2:$E$20,$Y$7,$H$2:$H$20,"H")

in AE7

=SUMIFS($G$2:$G$20,$X$2:$X$20,"<>",$D$2:$D$20,$Y$7)+SUMIFS($F$2:$F$20,$X$2:$X$20,"<>",$E$2:$E$20,$Y$7)

plus Data Validation for TEAM selction (named range "Teams")

For Home Away : amend above formulae

**mansh34** · 06-15-2022, 06:36 AM

Oh thank you, lemme go through them, i will be back

**mansh34** · 06-15-2022, 07:30 AM

not working because if i wanted to get their last nth matches played, for example, Liverpool's last match played was on 8/21/21 Liverpool vs Burnley. the question now is how do I get the goals scored and conceded in their recent/last match/matches played for Liverpool?. i await for favorable response. thanks in advance

**JohnTopley** · 06-15-2022, 08:11 AM

You need to show expected results:Liverpool beat Norwich 3-0 and Burnley 2-0 so goals scored is 5, conceded 0 (as per results) - what do you expect?.

I am dropping out of this thread.

**mansh34** · 06-15-2022, 08:26 AM

Sorry if any inconveniences, I want if I say last 1 match played, it will only sum 8/21/21 Liverpool 2 vs Burnley 0 goals , if I say last 2 matches played, it will sum 8/21/21 Liverpool 2 vs and Burnley 0, 8/14/21 Norwich 0 vs Liverpool 3 goals repectively. Thanks

**JohnTopley** · 06-15-2022, 08:35 AM

Not what you requested and there is no cell specifing the required number of matches to be considered.

**mansh34** · 06-15-2022, 08:39 AM

Now you know what am talking about, help me out, I just want nth matches goals calculated dynamically

**JohnTopley** · 06-15-2022, 09:16 AM

Last try

HELPER

=IF(OR($D2=$Y$7,$E2=$Y$7),IF(COUNTIF($D$2:$E$20,$Y$7)-COUNTIF($D$2:E2,$Y$7)+1>$Z$2,"",COUNTIF($D$2:$E$20,$Y$7)-COUNTIF($D$2:E2,$Y$7)+1),"")

in AA7

=COUNTIFS($X$2:$X$20,"<=" &$Z$2,$D$2:$D$20,$Y$7,$H$2:$H$20,"H")+COUNTIFS($X$2:$X$20,"<=" &$Z$2,$E$2:$E$20,$Y$7,$H$2:$H$20,"A")

BOLD changed in other formula

**mansh34** · 06-15-2022, 02:55 PM

Thank you SO MUCH.. you just nailed it. i am very grateful. I was trying to get my way out on getting the Over/Under goals, BTTS, and putting the teams in a league team respectively, but here am exhausted with logics now, can you still help?

**JohnTopley** · 06-15-2022, 04:01 PM

the Over/Under goals, BTTS, and putting the teams in a league team

I haven't a clue what is meant by any of the above.

**mansh34** · 06-15-2022, 04:13 PM

I have uploaded an attachment to that

**JohnTopley** · 06-16-2022, 04:34 AM

No explanation of, [or sample data]

over 2.5 or BTTS

Re the league: you should be able to modify the formulas I gave ( row 7) for "Liverpool" selecting the teams in Z23 down.

AA23

=COUNTIFS($D$2:$D$20,$Z23)+COUNTIFS($E$2:$E$20,$Z23)

AB23

=COUNTIFS($D$2:$D$20,$Z23,$H$2:$H$20,"H")+COUNTIFS($E$2:$E$20,$Z23,$H$2:$H$20,"A")

etc

**mansh34** · 06-16-2022, 05:09 AM

the last formula you sent me just got me lost, I wanted a formula based on recent match not all, now I sent an attachment, in it is a recent form table with all the teams, which I want their Win, Draw, Loss, Goals scored/concede and BTTS calculated individually on that form table just like as I have outlined them, thank you for the efforts made so far in helping to solving my excel challenges. please Pardon me for am still learning

**JohnTopley** · 06-16-2022, 05:35 AM

This is my last post:

in X23

=MAX(MAX(IF($D$2:$D$20=$Z23,$B$2:$B$20)),MAX(IF($E$2:$E$20=$Z23,$B$2:$B$20)))

Enter with Ctrl+Shift+Enter

As you have 2019 you can use MAXIFS formula instead of the above

This will give the date of the most recent match played

You should now be able to fill in the formulae.

**mansh34** · 06-16-2022, 05:46 AM

THANk YOU! I appreciate your efforts, these are not want I ask for, sorry for any inconvenience. I will seek other helps too.

**JohnTopley** · 06-16-2022, 05:53 AM

Formulae here:

Ab23

=COUNTIFS($B$2:$B$20,$X23,$D$2:$D$20,$Z23,$H$2:$H$20,"H")+COUNTIFS($B$2:$B$20,$X23,$E$2:$E$20,$Z23,$H$2:$H$20,"A")

**mansh34** · 06-16-2022, 06:20 AM

i really see you want to help, maybe we are having miscommunication here. These Win, Draw, Loss, etc don't add up when I put nth matches played. just like you did formally but it should be on the table individually. Arsenal nth matches summed (Win, Draw, Loss, Goals, etc), Aston villa nth matches summed (Win, Draw, Loss, Goals, etc), then I will be able to drag the formula down.if you don't understand tell me, or maybe you can't help then lemme know as well, you have tried enough friend, you are a genius.

**JohnTopley** · 06-16-2022, 09:50 AM

Info for others: the "Nth" will not apply to all teams at any point in time, as some teams will have played less than N; equally some will have played more!.

And is maximumn value of "N" still to be 6?

AND please add sample expected results.

**mansh34** · 06-16-2022, 10:01 AM

Yes, the maximum should be 6 or preferably dynamic, for teams that have played less than the maximum nth matches, let the nth number they have played be displayed and summed, since is not up to the maximum (6) nth matches played. Thank you

**JohnTopley** · 06-16-2022, 10:17 AM

Please add some expected results to your file INCLUDING columns "Over 2.5" and "BTTS"

**mansh34** · 06-16-2022, 12:21 PM

Thank you, I have manually filled the table (this should be how I wanted it). I will create the Home and Away table once I get these ones correct. In cell AC20, is the nth matches played, I can input or use dropdown there for any recent nth matches I want, if I get it like this, I will try and follow up from there. Thank you once again as I await your response

**JohnTopley** · 06-16-2022, 01:16 PM

See attached ("Data2")

HELPER

=LARGE((IF($D$2:$D$20=$Z7,$B$2:$B$20))+(IF($E$2:$E$20=$Z7,$B$2:$B$20)),MIN($Z$2,$AA7))

Entered with Ctrl+Shift+Enter

Still no explanation of last 2 columns

**mansh34** · 06-16-2022, 01:54 PM

ok, thank you, for the last two columns, "Over 2.5" and "BTTS", Over2.5( is where home team and away team goals scored together is 3+), while BTTS(is where the home team scored 1+ and the away team scored 1+ separately). to explain more on this, Over 2.5 goals is where the match result between home and away team total goals ending in 3 or more goals, while both teams to score(BTTS) is where the home team scored a goal and away team scored a goal too.

**mansh34** · 06-17-2022, 01:44 PM

THANK YOU SOOO MUCH, I have gone through them and they suit what I needed, for the last two columns, "Over 2.5" and "BTTS", Over2.5( is where home team and away team goals scored together is 3+), and BTTS(is where the home team scored 1+ and the away team scored 1+ separately), Over 2.5 goals is where the match result between home and away team total goals ending in 3 or more goals, and both teams to score(BTTS) is where the home team scored a goal and away team scored a goal.I hope you can help with them too. I appreciate your effort so far

**JeteMc** · 06-22-2022, 10:40 PM

This proposal employs two helper columns (X:Y) which may be moved and/or hidden for aesthetic purposes.
Column X is populated using: =(SUM(F2:G2)>2.5)+0
Column Y is populated using: =AND(F2>0,G2>0)+0
The "over 2.5" and "BTTS" columns are populated using: =SUMPRODUCT(($B$2:$B$20>=$Z7)*(($D$2:$D$20=$AB7)+($E$2:$E$20=$AB7))*(X$2:X$20))
Let us know if you have any questions.

**JohnTopley** · 06-23-2022, 12:32 AM

In Ag

=SUMPRODUCT(($B$2:$B$20>=$X7)*(($D$2:$D$20=$Z7)+($E$2:$E$20=$Z7))*(($F$2:$F$20+$G$2:$G$20)>=3))

in AH

=SUMPRODUCT((($B$2:$B$20>=$X7)*($D$2:$D$20=$Z7)*($F$2:$F$20>=1)*($G$2:$G$20>=1))+(($E$2:$E$20=$Z7)*($F$2:$F$20>=1)*($G$2:$G$20>=1)))

**mansh34** · 06-23-2022, 08:10 AM

Thank you very much, i will get back to you soon.

**mansh34** · 06-23-2022, 08:10 AM

Thank you very much,lemme get back to you soon.

**mansh34** · 06-29-2022, 01:33 AM

Please house, any other steps to take now that I have got my challenge solved?.

**JohnTopley** · 06-29-2022, 02:50 AM

Go to "Thread Tools" at top of the page to mark as SOLVED,

calculate the goal scored and conceded in the last 6 football Matches

LinkBack

Thread Tools

Rate This Thread

Display

calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Re: calculate the goal scored and conceded in the last 6 football Matches

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions