# Consecutive win/loss and current win/loss streak

1. ## 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

2. ## 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.

3. ## Re: Consecutive win/loss and current win/loss streak

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

4. ## 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. ## Re: Consecutive win/loss and current win/loss streak

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

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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Consecutive win/loss and current win/loss streak

Originally Posted by TK2013

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. ## 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. ## Re: Consecutive win/loss and current win/loss streak

You're welcome. Thanks for the feedback!

14. ## Re: Consecutive win/loss and current win/loss streak

Originally Posted by Tony Valko
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. ## 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. ## 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. ## Re: Consecutive win/loss and current win/loss streak

You're welcome. Thanks for the feedback!

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

#### 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