Wins in the Last 5

  • 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: )
  • Please share your solution for the benefit of all. Thanks.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • 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

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!




    Read this to understand why we ask you to do this



    Understand cross posting