Posts by showtimesaints

    I've found an alternative on how to calculate goals for, the formula is,


    =SUM(IF(A:B=G2,IF(ROW(A:B)>=LARGE(IF(A:B=G2,ROW(A:B)),1),C:D)))



    This here gives me the goals for in the last game. But is there anyway to calcuate the goals against in the last game? Im having trouble to finding the opposite.


    Thanks

    Good morning thanks for bringing this up. I have attached the workbook with the resolution. The formula is as follows,


    For wins,

    =SUMPRODUCT(($B$2:$C$500=$L2)*($H$2:$I$500="W")*(ROW($B$2:$C$500)>=AGGREGATE(14,6,ROW($B$2:$C$500)/($B$2:$C$500=$L2),5)))


    For losses,

    =SUMPRODUCT(($B$2:$C$500=$L2)*($H$2:$I$500="L")*(ROW($B$2:$C$500)>=AGGREGATE(14,6,ROW($B$2:$C$500)/($B$2:$C$500=$L2),5)))

    Files

    • ATS NFL-2.xlsx

      (24.34 kB, downloaded 26 times, last: )

    Ok so I've noticed a problem with this formula and was wondering for some help. When I tried to do this for soccer it gave me an error. Because there are many games with 0 goals I was noticing that it was not adding up correctly and I would get an error. I attached another book with the last set of games of the season. I only did Last 1 game as reference so you can see the error. When I did this for Last 5, the teams that had 0 goals for or 0 goals against it was not adding it up correctly.


    Any chance to correct this?

    Files

    • Problem.xlsx

      (11.13 kB, downloaded 25 times, last: )

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    crossposted: https://www.excelforum.com/exc…-5-games.html#post5527795


    Good morning, the last post the help was amazing, thank you very much for that!! Was hoping with help for 2 more things, I want to find the following,


    -ATS(against the spread) wins and ATS losses in the last 5 games.


    -ATS Streak


    I have a formula inputed in the workbook for the Texans but i'm not sure if that is correct. As games are played I add the data to the bottom of the columns.

    Any help again will be so much. appreciated.


    Thanks

    Files

    • ATS NFL.xlsx

      (20.38 kB, downloaded 32 times, last: )

    WOW! that was awesome, thank you very much for this! it worked. the file you sent I will use that and add it to my worksheet. I'm still trying to learn excel and thankfully coming across someone as your self who has helped out has made it easier, thanks!!

    Thanks for getting back. Perhaps if I break it up and use Home and Away columns then I can break it down. But the formula I am using is


    {=AVERAGE(IF(Sheet1!$A$2:$B$889=I2,IF(ROW(Sheet1!$A$2:$B$889)>=LARGE(IF(Sheet1!$A$2:$B$889=I2,ROW(Sheet1!$A$2:$B$889)),5),Sheet1!$C$2:$D$889)))}


    Where I2 is the team that I am searching for (ie, Angels). But how do I write the formula to find Angels opponents scoring?

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    https://www.excelforum.com/exc…t-streak.html#post5538314


    Hello, was hoping for help, it would be so greatly appreciated. Im trying to figure out how to run in excel a formula that outputs the average scoring against per team in sports in the teams last 5 games and last 10 games.. For example, I want to see the Rays opponents scoring is in the last 5 and 10 games. I have the formula for scoring FOR but can't seem to figure out how to run scoring AGAINST.


    I've attached an excel sheet and its columns K and M that im trying to figure out.


    Thank you very much if someone can help!

    Files

    • Book3.xlsx

      (30.6 kB, downloaded 36 times, last: )