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.

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.

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.

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

Ok
This one will work.
Click on attachment