+ Reply to Thread
Results 1 to 44 of 44

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

  1. #1
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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
    Last edited by 6StringJazzer; 06-11-2022 at 05:30 PM. Reason: better titles please

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,626

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

    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

  3. #3
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    A sample including at LEAST 6 matches for the selected team will help.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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
    Last edited by mansh34; 06-12-2022 at 03:09 PM. Reason: Error

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,626

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

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

    Pete

  7. #7
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,626

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

    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

  9. #9
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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

  10. #10
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,626

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

    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

  12. #12
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  13. #13
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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...

  14. #14
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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?

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  17. #17
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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.

  19. #19
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

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

  21. #21
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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?

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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.

  25. #25
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    I have uploaded an attachment to that
    Attached Files Attached Files

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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
    Last edited by JohnTopley; 06-16-2022 at 05:14 AM.

  27. #27
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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.
    Last edited by JohnTopley; 06-16-2022 at 05:37 AM.

  29. #29
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  30. #30
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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")
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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.

  32. #32
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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.
    Last edited by JohnTopley; 06-16-2022 at 10:10 AM.

  33. #33
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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
    Last edited by mansh34; 06-16-2022 at 10:07 AM.

  34. #34
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

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

  35. #35
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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
    Attached Files Attached Files

  36. #36
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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
    Attached Files Attached Files
    Last edited by JohnTopley; 06-16-2022 at 01:19 PM.

  37. #37
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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.

  38. #38
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

    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

  39. #39
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,097

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

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  40. #40
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

    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)))
    Attached Files Attached Files

  41. #41
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  42. #42
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  43. #43
    Registered User
    Join Date
    05-22-2022
    Location
    Nigeria
    MS-Off Ver
    2019
    Posts
    26

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

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

  44. #44
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,909

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1