+ Reply to Thread
Results 1 to 3 of 3

Wins vs. Specific Opponent and Counting Streaks and Last 5/10 Games

  1. #1
    Registered User
    Join Date
    10-08-2016
    Location
    stockton, california
    MS-Off Ver
    2013
    Posts
    2

    Wins vs. Specific Opponent and Counting Streaks and Last 5/10 Games

    I've created a spreadsheet to keep track of our online league, but I've ran into trouble trying to create a formula or helper columns to count records vs. division opponents and conference opponents. I also have ran into trouble calculating current winning/losing streak and last 5 game results. I've tried multiple attempts at using helper columns and counting 1 and -1 for wins or losses. I've attached my current worksheet, if anyone could point me in the right direction.

    I feel like I'm close, but for example counting Winning/Losing streak it wants to go from W3 to L0. I also have to account for Ties and Byes to continue streak.

    Thanks for any assistance. I've used a few of the solutions from here in this current workbook and others, so thanks for those as well.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Wins vs. Specific Opponent and Counting Streaks and Last 5/10 Games

    Sorry for off-topic interjection:
    Unfortunately, you haven't posted any of your formulas or described what they are supposed to calculate.

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    10-08-2016
    Location
    stockton, california
    MS-Off Ver
    2013
    Posts
    2

    Re: Wins vs. Specific Opponent and Counting Streaks and Last 5/10 Games

    I knew I forgot to do something.

    Details of the attached workbook.

    I have 3 setup pages currently. Team Name Setup, League Games Setup and Standings setup.League Games Setup is for every game for the season and it is set up to display:

    NFL week(1-17), game#(to use for vlookup on the presentation page displaying a clean look, day of week, time, away team, away score,home score and venue.

    The venue column is using =IFERROR(VLOOKUP($H6,'Team Name Setup'!$B$3:$D$34,3,FALSE),"Home Stadium"). The score is pulled from the weekly schedule to allow other users to input their score and have a cleaner presentation.

    My Standings setup page keeps track of stats such as Wins, Losses, Ties, Games Played, Win %. It is then broken down even further for Home W-L-T Home Points For/Against, Road W-L-T, PF/PA and Home Ties/Road Ties. I have a few helper columns to help rank the teams in a clean presentation page of the standings. Below is an example of what I use to calculate a teams home wins:
    =SUMPRODUCT(('League Games Setup'!$H$3:$H$258 ="New England")*('League Games Setup'!$G$3:$G$258>'League Games Setup'!$F$3:$F$258)) The > is flipped to < for home losses. This is an example of what I use to find the points for/against: =SUMIF('League Games Setup'!$H$3:$H$258,"=New England",'League Games Setup'!$G$3:$G$242). Same thing here to find the points againts Column G (home score) will be changed to Column F (away score). The same formulas are used to find Road/Home W-L-T PF/PA.

    For the W L T GP PCT columns I will just add the columns for the W-L-T. Add the W-L-T columns to get games played. =IFERROR(((D3+F3/2)/G3),0) is used to find the winning percentage.

    Where I am having trouble is using the league games setup page and finding out division opponents. For Example, New England's division opponents would be Miami, N.Y. Jets and Buffalo. I've tried different formulas mainly sumproduct/countifs/index/match, but everything has come up #Value. The same thing for conference W-L-T, so in New England's case it would be every other AFC team. I feel like sumproduct will do the trick, but once I start adding multiple arrays to look for specific names in multiple columns is where I receive invalid errors.

    The other issue is trying to calculate the winning/losing streak. Currently starting at AX on the Standings Setup I have every team name and 2 different ways to count each weeks win or loss. I recently just added to the League Games Setup a column to name the winner. I've now taken that column and starting in AY on the Standings Setup I started determining each teams win/loss per week using =IF(COUNTIF('League Games Setup'!$J$3:$J$18,$AX3),"1","-1"). The other formula I'm experiencing with is =IF(COUNTIF('League Games Setup'!$J$3:$J$18,$AX4),"Win","Loss"). I would prefer to keep it "Win""Loss", but I'm open to all suggestions. I've manually had to add each teams Bye week in their correct cell and still working on automating this as well.

    When it comes to counting the win streak I've formatted the cells using a custom number "W"0;"L"0. For the first game streak I'm using =IF(AY$3="-1",-1,1) or =IF($AY25="Win",1,-1). From week's 2 to 17 I use =IF(AZ$3="-1",AY$3-1,1) or =IF($AZ25="Win",1,-1-1). Everything seems good until the streak changes (W->L or L>W). Once the change happens it reverts to W0 or L0. I thought this was my custom number format, but changing it to "W"1;"L"1 didn't help.

    Once I got further down the rabbit hole I started incorporating =IF(BG$35="Win",BW$35+1,IF(BG$35="Bye",BW$35,-1)) to account for the Bye week to keep the streak in tact, but then frustration began to take over.

    I did attempt using =MAX(FREQUENCY(IF((INDEX('League Games Setup'!$A$3:$I$258,0,8)=$AK2)+(INDEX('League Games Setup'!$A$3:$I$258,0,5)=$AK2),IF((SIGN(((INDEX('League Games Setup'!$A$3:$I$258,0,7))>(INDEX('League Games Setup'!$A$3:$I$258,0,6))))=IF(INDEX('League Games Setup'!$A$3:$I$258,0,8)=$AK2,$AK3,-$AK3)),ROW('League Games Setup'!$A$3:$I$258))),IF((INDEX('League Games Setup'!$A$3:$I$258,0,8)=$AK2)+(INDEX('League Games Setup'!$A$3:$I$258,0,5)=$AK2),IF((SIGN(((INDEX('League Games Setup'!$A$3:$I$258,0,7))>(INDEX('League Games Setup'!$A$3:$I$258,0,6)))))<>IF(INDEX('League Games Setup'!$A$3:$I$258,0,8)=$AK2,$AK3,-$AK3),ROW('League Games Setup'!$A$3:$I$258)))))*$AK3 until I realized that I was just trying to find the longest winning/losing streak of the season and it wasn't for the current.

    The last issue that I was currently facing was to find the results of the last 5 games played and with custom number to display as W-L. After some tweaking I arrived at =SUM(IF((INDEX('League Games Setup'!$E$3:$I$258,0,4)="New England")+(INDEX('League Games Setup'!$E$3:$I$258,0,1)="New England"),IF((INDEX('League Games Setup'!$E$3:$I$258,0,3))>(INDEX('League Games Setup'!$E$3:$I$258,0,2)),IF(INDEX('League Games Setup'!$E$3:$I$258,0,4)="New England",1,5),IF(INDEX('League Games Setup'!$E$3:$I$258,0,4)="New England",1/20,1)))) but it started becoming incorrect when more games were added.


    I have no problem entering some of this stuff manually, but with more auto populating, the less work once the workbook is completed. I am willing to add more helper columns, correcting anything that doesn't look proper and take any/all advice to become more proficient at Excel formulas.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting streaks in Excel
    By Danielpeam in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-30-2016, 06:21 PM
  2. Counting Wins and Losses
    By chester1993 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-28-2016, 09:52 PM
  3. Exclude zero when counting streaks (win/loss) and current streaks
    By poko10 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-11-2013, 06:06 AM
  4. [SOLVED] Calculating streaks of "WINS" & "LOSSES"?
    By domgilberto in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2013, 11:25 AM
  5. Need to make a wins vs played spreadsheet for multiples games/players
    By Magnerss in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2013, 04:29 PM
  6. Replies: 6
    Last Post: 02-04-2012, 06:57 PM
  7. Replies: 0
    Last Post: 09-27-2011, 10:31 AM

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