# Identifying Current Streaks of Goals/Points

1. ## Identifying Current Streaks of Goals/Points

What I'm asking is somewhat similar to what was discussed here: http://www.excelforum.com/excel-gene...ss-streak.html

I have a work book which tracks stats for a hockey team I work for. Each player has their own page with game logs, tracking goals, goal types, penalties and other hockey stats. I'm looking to calculate goal and point streaks off this.

I've included an example of what I'm looking for.
streak example.xlsx

I want to clarify that I'm not looking for the longest streak. Using the above example, if it's October 13th and this player gets 1 assist, then the goal streak goes to 0, the assist streak goes to 2 and the point streak goes to 6.

2. ## Re: Identifying Current Streaks of Goals/Points

Hi alex,

See the attached file, yellow cell.
For other two calculation, I am not clear - please provide more details. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

3. ## Re: Identifying Current Streaks of Goals/Points

Hi DILIPandy

We're on the right track, but not quite what I'm looking for. Your calculation attached above shows the highest streak in a given range, not the 'current' streak.

In column C, I'm looking for the current streak, ie: consecutive numbers >1 from the bottom up. If I add '1' to C10, your value in I4 goes to 5, which is correct. However, if I add '0' in C10, then the value in I4 stays at 4, but it should be '0'.

In terms of your question, the other two categories are the same calculation, just related to columns D and E respectively. Once I can get one working, the others will be just copies.

4. ## Re: Identifying Current Streaks of Goals/Points

Ok.. so let me rephrase your query:-

You want the sum of consecutive 1s, no matter if they are 4 in count or more than that... but if there is 0, then the answer should be 0.

Regards,
DILIPandey

<click on below 'star' if this helps>

5. ## Re: Identifying Current Streaks of Goals/Points

Correct, but not limited to '1', but '>=1'. So if C2=1 and C3=2, then I4=2. Then later, if C4=0, then I4=0. Then later if C5=1, C6=1, C7=1, then I4=3. Looking for the consecutive number of values >=1 from the bottom of the list up.

6. ## Re: Identifying Current Streaks of Goals/Points

Hi

Try this
Cell I4 =MAX(FREQUENCY(IF(C2:C10<>0,ROW(C2:C10)),IF(C2:C10=0,ROW(C2:C10)))) confirmed with CONTROL+SHIFT+ENTER
Cell I5 =MAX(FREQUENCY(IF(D2:D10<>0,ROW(D2:D10)),IF(D2:D10=0,ROW(D2:D10)))) confirmed with CONTROL+SHIFT+ENTER
Cell I6 =MAX(FREQUENCY(IF(E2:E10<>0,ROW(E2:E10)),IF(E2:E10=0,ROW(E2:E10)))) confirmed with CONTROL+SHIFT+ENTER

Good luck.

7. ## Re: Identifying Current Streaks of Goals/Points

Originally Posted by micope21
Hi

Try this
Cell I4 =MAX(FREQUENCY(IF(C2:C10<>0,ROW(C2:C10)),IF(C2:C10=0,ROW(C2:C10)))) confirmed with CONTROL+SHIFT+ENTER
Cell I5 =MAX(FREQUENCY(IF(D2:D10<>0,ROW(D2:D10)),IF(D2:D10=0,ROW(D2:D10)))) confirmed with CONTROL+SHIFT+ENTER
Cell I6 =MAX(FREQUENCY(IF(E2:E10<>0,ROW(E2:E10)),IF(E2:E10=0,ROW(E2:E10)))) confirmed with CONTROL+SHIFT+ENTER

Good luck.
Hi Micope

Thanks for your effort, but again it's not quite what I'm looking for. Your formula shows that max streak through a range, however when C10=0, thus the streak of >=0 is broken, the result in I4 should be 0, not 4 (the max in the range).

8. ## Re: Identifying Current Streaks of Goals/Points

Ok
This one will work.
Click on attachment

9. ## Re: Identifying Current Streaks of Goals/Points

Massive followup bump!

Is there a way to reverse this? To show slumps instead of streaks? Essentially for looking for consecutive zero's, instead of consecutive >=1.

This is the formula that provides my streaks.

=OFFSET('2 SMITH'!F3,COUNT('2 SMITH'!F4:F59),0,1,1)

10. ## Re: Identifying Current Streaks of Goals/Points

Hi

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