# Exclude zero when counting streaks (win/loss) and current streaks

1. ## Exclude zero when counting streaks (win/loss) and current streaks

Hi,

I had been trying to figure out calculating streaks (win/loss) for my football's team statistics and is facing the following 2 issues.

My example:

Column: A B C D E F G H I J K L M N O P Q R
Row 1: w w w L 0 L 0 L 0 w w w 0 0 w L 0 L

0 denotes non-appearance and thus should not be considered in the calculation.

1) To find the current streak. Desired Result - 2L
I had been using the below formula to find the current streak but the formula takes zero into consideration and gives me the result as 1L. How can i make the forumla to ignore the zeros in the array?

=LOOKUP(1E+100,FREQUENCY(IF(A1:R1=LOOKUP("zzz",A1:R1),COLUMN(A1:R1)),IF(A1:R1<>LOOKUP("zzz",A1:R1),COLUMN(A1:R1))))&""&LOOKUP("zzz",A1:R1)

2) To find the longest winning or losing streak. Desired result: 4w and 3L
Similarly, I had been using the below formula to find the longest winning and losing streak but the formula takes zero into consideration and give me the result as 3w and 1L.
How can i make the forumla to ignore the zeros in the array?

Longest Winning Streak:
=MAX(FREQUENCY(IF(A1:R1="W",COLUMN(A1:R1)),IF(A1:R1<>"W",COLUMN(A1:R1))))

Longest Losing Streak:
=MAX(FREQUENCY(IF(A1:R1="l",COLUMN(A1:R1)),IF(A1:R1<>"l",COLUMN(A1:R1))))

Thank you so much!

Best Regards,
Poko.  Register To Reply

2. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

Can you upload example? We a quite lazy for retyping whole post   Register To Reply

3. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

Hi Zbor,

Best Regards,
Poko  Register To Reply

4. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

Hi Zbor,

Any solutions to my problems?

Thank you so much!  Register To Reply

5. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

Q2 is a relatively easy fix, instead of <>"W" in the second IF you need ="L", i.e.

=MAX(FREQUENCY(IF(A1:R1="W",COLUMN(A1:R1)),IF(A1:R1="L",COLUMN(A1:R1))))

Same formula for losing streak except swap "W" and "L"

A little trickier for Q1 but this should fix it along the same lines....

=LOOKUP(1E+100,FREQUENCY(IF(A1:R1=LOOKUP("zzz",A1:R1),COLUMN(A1:R1)),IF(A1:R1=IF(LOOKUP("zzz",A1:R1)="W","L","W"),COLUMN(A1:R1))))&""&LOOKUP("zzz",A1:R1)

...or this version would be shorter assuming Excel 2007 or later

=COUNTIF(INDEX(A1:R1,IFERROR(MATCH(2,1/(A1:R1=IF(LOOKUP("zzz",A1:R1)="L","W","L"))),1)):R1,LOOKUP("zzz",A1:R1))&LOOKUP("zzz",A1:R1)  Register To Reply

6. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

Wow! The formulas work perfectly fine!

Thank you so much!

Best Regards,
Poko  Register To Reply

7. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

I was wondering how to get the current streak formula to work if the data is entered vertically instead of horizontally. I appreciate the help!

current streak.xlsx  Register To Reply

8. ## Re: Exclude zero when counting streaks (win/loss) and current streaks

PreLives,  Register To Reply