+ Reply to Thread
Results 1 to 17 of 17

Consecutive win/loss and current win/loss streak

  1. #1
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Consecutive win/loss and current win/loss streak

    I have Column A B C
    Col A and Col B contains value where I manually input data.
    Col C calculates based on data in Col A and B. All the rows in the Col C does have the formula to calculate, but will show only if there is data in Col A and B. =If(a2="","",a2+b2)

    I googled to get some idea and tried this formula (refer attachment), but it considers all the rows (even the rows those doesn't have any value to display, just the formulas) in Col C and calculates.

    And tried another to find current streak, but that's not what I am looking for..

    I don't have a clue here...

    Any help will really appreciated...

    sample.xlsx
    Last edited by TK2013; 09-18-2013 at 07:09 AM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    In Excel TEXT has a higher value than ANY number.

    The formula blanks are empty TEXT strings and as such have a higher value than ANY number.

    So, when you test the range for being >0, the formula blanks are greater than 0 therefore the logical test will be TRUE causing an incorrect result.

    Add a test to make sure the cells contain numbers. Like this:

    =MAX(FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11>0,ROW(C$2:C$11))),IF(C$2:C$11<0,ROW(C$2:C$11))))

    Still array entered.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Consecutive win/loss and current win/loss streak

    Edit - disregard (hadn't seen Tony Valko reply)


    Maybe...

    =MAX(FREQUENCY(IF($C$2:$C$11<>"",IF($C$2:$C$11>0,ROW($C$2:$C$11))),IF($C$2:$C$11<0,ROW($C$2:$C$11))))
    Ctrl+Shift+Enter
    Last edited by mlcb; 09-05-2013 at 08:51 PM.
    Marcelo Branco

  4. #4
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    Thank you Tony and MLCB.

    Both the formulas works... Thanks for ISNUMBER and <>""

    Now I tried these formulas to find the current streak...

    CELL I2
    =IF($C$2:$C$11<>"",IF(Sheet1!C2>0,IF(Sheet1!C1>0,Sheet1!I1+1,1),IF(Sheet1!C1<0,Sheet1!I1-1,-1)),"")

    and click/dragged the formula till CELL I11

    CELL F4
    =INDEX(I2:I11,MATCH(9.99999999999999E+307,I2:I11))

    Is there any way I can make this compact. Rather than creating a column and refering that column to find the current streak. (Like combining all the above (I2:I11) and F4 and put together in F4 as one formula)

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    It would be easier to use an additional column that records a W or L.

    Here's what you asked for...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    2
    12
    -6
    6
    _____
    Consecutive Win
    5
    3
    2
    -6
    -4
    _____
    Consecutive loss
    2
    4
    -8
    -6
    -14
    _____
    Current win/loss Streak
    5 W
    5
    14
    -5
    9
    _____
    6
    15
    -1
    14
    _____
    7
    3
    0
    3
    _____
    8
    5
    1
    6
    _____
    9
    2
    0
    2
    _____

    All formulas are array formula**.

    This formula entered in F2:

    =MAX(FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11>0,ROW(C$2:C$11))),IF(C$2:C$11<0,ROW(C$2:C$11))))

    This formula entered in F3:

    =MAX(FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11<0,ROW(C$2:C$11))),IF(C$2:C$11>0,ROW(C$2:C$11))))

    This formula entered in F4:

    =LOOKUP(1E+100,FREQUENCY(IF(ISNUMBER(C2:C11),IF(SIGN(C2:C11)=SIGN(LOOKUP(1E+100,C2:C11)),ROW(C2:C11))),IF(ISNUMBER(C2:C11),IF(SIGN(C2:C11)<>SIGN(LOOKUP(1E+100,C2:C11)),ROW(C2:C11)))))&" "&IF(LOOKUP(1E+100,C2:C11)<0,"L","W")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    I'm curious about something. What if the value in column C is 0? You (and we) haven't accounted for that in the formulas. Is 0 a possible result in column C? If so, is 0 considered a positive value?

  6. #6
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    Thank you...

    Yeah, it now seems easier to have a separate column for that. But I like your formula and gonna stick with it. I want everything put together in one place.

    Nice point. I haven't considered "0", I didn't thought of that. Thank you.

    The formulas in F2 and F3 don't consider "0", totally ignoring it (it seems so).
    The formula in F4 recognizing the value "0" and take it as a positive value. But resets the streak count when it sees "0". And go on with the streak count (1 W, 2 W...) as long as the previous value is also "0". Again resets to 1 W when a positive value comes after "0".

    Ok, now we take "0" as a negative.
    I replace "<" with "<=" in formula F2 and F3 (correct me if I am wrong)

    I don't have a clue how to add this criteria in formula F4

    Thanks for your time. I really appreciate it. And a special thanks for summarizing all the three formulas in one post.

    Note: If considering "0" as a positive value helps making the formula easier, I have no problem in that.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    I don't know what this data represents but it seems to me that a 0 would be the equivalent of a tie. So you have Wins, Losses and Ties.

    Data Range
    A
    B
    C
    D
    1
    2
    10
    5
    15
    Win
    3
    5
    -10
    -5
    Loss
    4
    5
    -5
    0
    Tie

    Does that make sense?

  8. #8
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    No, there is no tie in this game.

    Zero is very less likely to come and it's even rarer to appear consecutively.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    Ok, no ties!

    This version counts 0s as a win.

    Data Range
    A
    B
    C
    D
    E
    F
    1
    2
    12
    -13
    -1
    ____
    Consecutive Win
    2
    3
    2
    -6
    -4
    ____
    Consecutive loss
    3
    4
    -8
    -6
    -14
    ____
    Current win/loss Streak
    2 W
    5
    14
    -5
    9
    ____
    6
    15
    -15
    0
    ____
    7
    3
    -4
    -1
    ____
    8
    5
    3
    8
    ____
    9
    2
    -2
    0
    ____
    10
    11

    All formulas are array formulas**.

    F2:

    =MAX(FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11>=0,ROW(C$2:C$11))),IF(C$2:C$11<0,ROW(C$2:C$11))))

    F3:

    =MAX(FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11<0,ROW(C$2:C$11))),IF(C$2:C$11>0,ROW(C$2:C$11))))

    F4:

    =LOOKUP(E1+100,IF(LOOKUP(1E+100,C2:C11)>=0,FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11>=0,ROW(C$2:C$11))),IF(C$2:C$11<0,ROW(C$2:C$11))),FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11<0,ROW(C$2:C$11))),IF(C$2:C$11>0,ROW(C$2:C$11)))))&" "&IF(LOOKUP(1E+100,C2:C11)<0,"L","W")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  10. #10
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    Wonderful...

    Thank you...

    So >= should only be in F2, no need of that in F3... Correct?

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    Quote Originally Posted by TK2013 View Post

    So >= should only be in F2, no need of that in F3... Correct?
    Ooops!

    Yes, >= should be in there!

    F3 formula:

    =MAX(FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11<0,ROW(C$2:C$11))),IF(C$2:C$11>=0,ROW(C$2:C$11))))

  12. #12
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    Thanks a lot...

    Thanks for your time, I really appreciate it. This is the second time you helped me in two weeks.

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    You're welcome. Thanks for the feedback!

  14. #14
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    Quote Originally Posted by Tony Valko View Post
    Data Range
    A
    B
    C
    D
    E
    F
    1
    2
    12
    -13
    -1
    ____
    Consecutive Win
    2
    3
    2
    -6
    -4
    ____
    Consecutive loss
    3
    4
    -8
    -6
    -14
    ____
    Current win/loss Streak
    2 W
    5
    14
    -5
    9
    ____
    6
    15
    -15
    0
    ____
    7
    3
    -4
    -1
    ____
    8
    5
    3
    8
    ____
    9
    2
    -2
    0
    ____
    10
    11

    All formulas are array formulas**.

    =LOOKUP(E1+100,IF(LOOKUP(1E+100,C2:C11)>=0,FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11>=0,ROW(C$2:C$11))),IF(C$2:C$11<0,ROW(C$2:C$11))),FREQUENCY(IF(ISNUMBER(C$2:C$11),IF(C$2:C$11<0,ROW(C$2:C$11))),IF(C$2:C$11>0,ROW(C$2:C$11)))))&" "&IF(LOOKUP(1E+100,C2:C11)<0,"L","W")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    An issue here...

    Current streak works as long as it's a win. As soon as a loss comes, it turns "0 L" and stays @ "0 L" until another win comes.

    When I tried just the numbers (no formulas) in COLUMN C it works. I mean it recognizes loss and counts the losing streak. But in our excel sheet, COLUMN C is not filled with just numbers but with formulas
    =IF(A2="","",A2+B2)
    How can we fix this issue?

  15. #15
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    I'm kind of confused on this.

    Let's use a W/L column which makes things much easier.

    Data Range
    A
    B
    C
    D
    E
    F
    1
    2
    12
    -13
    -1
    L
    Consecutive Win
    4
    3
    2
    -6
    -4
    L
    Consecutive loss
    3
    4
    -8
    -6
    -14
    L
    Current win/loss Streak
    1 L
    5
    14
    -5
    9
    W
    6
    15
    -15
    0
    W
    7
    3
    1
    4
    W
    8
    5
    0
    5
    W
    9
    2
    -3
    -1
    L
    10
    11

    This formula entered in D2 and copied down as needed:

    =IF(COUNT(C2),IF(C2<0,"L","W"),"")

    These array formulas** for the streaks:

    F2:

    =MAX(FREQUENCY(IF(D2:D11="W",ROW(D2:D11)),IF(D2:D11<>"W",ROW(D2:D11))))

    F3:

    =MAX(FREQUENCY(IF(D2:D11="L",ROW(D2:D11)),IF(D2:D11<>"L",ROW(D2:D11))))

    F4:

    =LOOKUP(1E+100,FREQUENCY(IF(D2:D11=LOOKUP(2,1/(D2:D11<>""),D2:D11),ROW(D2:D11)),IF(D2:D11<>LOOKUP(2,1/(D2:D11<>""),D2:D11),IF(D2:D11<>"",ROW(D2:D11)))))&" "&LOOKUP(2,1/(D2:D11<>""),D2:D11)

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  16. #16
    Registered User
    Join Date
    03-09-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Consecutive win/loss and current win/loss streak

    Thanks again Tony...

    No problem with the consecutive wins/loss from the previous formulas. They are working fine... Just this current streak formula couldn't find the values of formula-filled-BLANK-display cells.

    F4 is the one I am looking for, and it works... Thank you...

  17. #17
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Consecutive win/loss and current win/loss streak

    You're welcome. Thanks for the feedback!

+ 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] Win Loss Percentage and Last 20 Streak
    By chemmiah in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-15-2017, 06:27 AM
  2. Replies: 7
    Last Post: 08-03-2013, 09:51 AM
  3. Combining Win/Loss Streak with location
    By hammerb in forum Excel General
    Replies: 7
    Last Post: 10-08-2012, 08:13 AM
  4. Win and loss streak
    By wongum in forum Excel General
    Replies: 2
    Last Post: 01-29-2012, 06:59 PM
  5. CURRENT Win/Loss Streak
    By lil_ern63 in forum Excel General
    Replies: 14
    Last Post: 10-19-2011, 06:54 PM

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