+ Reply to Thread
Results 1 to 7 of 7

Streak on Wins and Losses

  1. #1
    Registered User
    Join Date
    10-19-2011
    Location
    Port Charlotte, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Streak on Wins and Losses

    Sorry that I broke a rule before I made this tread. I will not do that again.

    I have been working on making formula on streak on wins and losses on my excel for football statistics. The formula I am using is:

    "=(INDEX(G10:G26,COUNT(H10:H26))&(INDEX(H10:H26,COUNT(H10:H26))))"

    The result came out as L4 (lost 4 straight games), that result is just what I want, but the problem is when I put in the final score on next game, the result came out blank. I couldn't figure the problem.

    Thanks

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Streak on Wins and Losses

    Do you have an example workbook you could post?

  3. #3
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Streak on Wins and Losses

    You'll probably need to attach a sample of the workbook for us to be able to ascertain the problem.

  4. #4
    Registered User
    Join Date
    10-19-2011
    Location
    Port Charlotte, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Streak on Wins and Losses

    On my worksheet, you will see that formula I just mentioned is on cell T9 on "NFC West" worksheet. It is a draft work. I will move that formula to "Standings" worksheet after I get that formula to work. The letters and number outside of the borders will be invisible by using font color same as background color.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Streak on Wins and Losses

    Sorry to take so long getting back to you - busy day yesterday.

    I see what you're trying to do, but the presence of the "bye" row means that the count function returns the wrong row number.

    The formula that works for me is =INDEX(G:G,MAX(INDEX(ROW(H10:H26) *(H10:H26<>"") * (OFFSET(H10:H26,1,0)=""),0)),1) & INDEX(H:H,MAX(INDEX(ROW(H10:H26) *(H10:H26<>"") * (OFFSET(H10:H26,1,0)=""),0)),1)

    Not as simple, but it does seem to do the job.

    You can simplify it by putting in another helper column. In I10 put the formula =IF(SUM($H$10:$H$26)=SUM($H$10:H10),TRUE,FALSE) and copy down. You can then simplify your streak formula to =INDEX(G10:G26,MATCH(TRUE,I10:I26,0),1) & INDEX(H10:H26,MATCH(TRUE,I10:I26,0))

    Whichever method you prefer, I suppose.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Streak on Wins and Losses

    As you are simply retrieving the last values in columns G and H you can use this formula

    =LOOKUP(2,1/(G10:G26&H10:H26<>""),G10:G26&H10:H26)

    To prevent an error when there are no scores add IFERROR

    =IFERROR(LOOKUP(2,1/(G10:G26&H10:H26<>""),G10:G26&H10:H26),"")

    Edit: actually even simpler, just get the combined value from the last row with a number in H10:H26

    =IFERROR(LOOKUP(9^9,H10:H26,G10:G26&H10:H26),"")
    Last edited by daddylonglegs; 10-21-2011 at 10:07 AM.
    Audere est facere

  7. #7
    Registered User
    Join Date
    10-19-2011
    Location
    Port Charlotte, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Streak on Wins and Losses

    Thanks for you help.

    It works great!!!

+ 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