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

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

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

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

Hi Zbor,

Thank you for your prompt reply!

Here's the spreadsheet!

Best Regards,
Poko

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

Hi Zbor,

Any solutions to my problems?

Thank you so much!

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)

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

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

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

PreLives,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

#### Thread Information

##### Users Browsing this Thread

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