Current Streak of "0's" in a list

[alexrawnsley]

I posted a while back about sports streaks, trying to get a cell to tell me how many consecutive games (or rows) there was a number >0. When a 0 was entered, the streak went back to 0. I'm tracking hockey stats, so consecutive games with a goal or a point.

Anyway, I'm wanting to reverse that to look at slumps.

See the attached file for an example, and the solution that someone found for me for the streaks. Instead of looking for consecutive numbers >0, I'm looking for consecutive zero's in a list. Not the max, the current.

In the attached example, you'll see the desired results, and the set up of my sheet. Adding to this example, if C10=1 (or more), then I9 should be 0. Likewise if D10 or E10=0, then I10 and I11 are 2.

I'm not concerned with assist drought. Just goals and points essentially.

streak example.xlsx

[benishiryo]

hi alexrawnsley. copy this formula into the formula bar of I9:

Please Login or Register  to view this content.

press CTRL + SHIFT + ENTER to confirm. if you need it for Point Streak, change all references of Column C to Column D. not sure what is I4:I6 referring to though

[alexrawnsley]

I4:I6 is the streak. The consecutive numbers >0. That's just a left over from my last problem.

I'm having a small issue. I put the formula where I needed it, and it calculated the right number. I then put a 1 in the next row and it zero'd like normal. But then when I put a zero underneath, it went to -1, where the result should be 1. Putting a 2nd 0 underneath and the result was 1.

Thoughts?

[jason.b75]

Try this one, array confirmed with Shift Ctrl Enter.

Please Login or Register  to view this content.

[alexrawnsley]

This gives me results like -2 and -6, instead of the number of 0's.

[alexrawnsley]

Here's a better example from where I'll be using the formula.

example.xlsx

As you can see, the 2 SMITH sheet is the list of numbers. A row for each game played, goals, assists, points for each game. The goals column (E) is a SUM of K:P. For this example, I'm only interested in E and I.

The formula I want is for Kings Streaks and Milestones:F5 and G5.

I essentially want it to look from top to bottom, and find the current streak of 0's. If a number >0 is entered in the next row (in this case, E24), then F5 goes to 0.

There are hidden columns for the streaks formula that someone was able to do for me. That feeds columns B:E on Kings Streaks and Milestones.

I hope this extended example makes sense. Under the current example, F5 on Streaks/Milestones should be '1'. If a 0 is entered in 2 SMITH:E24, F5 should be 2, etc, etc until a number >0 is entered in the E column of Streaks/Milestones. Once that is, the number in F5 reverts back to 0 and the process starts again.

[benishiryo]

your latest template affected my calculations because of the merged cells. try this now:

Please Login or Register  to view this content.

same thing. CTRL + SHIFT + ENTER to confirm.

basically, it searches if the last entry in Column E is 0. if it is, then it returns 0. otherwise it takes the Max row number where it finds the 0 minus the Max row number where it is not a 0. the problem occurs when my COUNTA returns 22 filled cells in column E because E3 of your cell is considered empty. hence, the range is E2:E22 instead of E23.

[alexrawnsley]

I'm clearly doing something wrong with this. I pasted the formula into a cell on the 2 SMITH page (off to the side) and it produced 18 when it should be 17, however working it in with more numbers, it brings results back perfectly!

[icestationzbra]

test and change ranges to suit.

CSE formula - array entered, more robust:

Please Login or Register  to view this content.

regular formula, seems to be as robust, but cannot vouch:

Please Login or Register  to view this content.

2^20 is the maximum number of rows available in Excel 2007.

on the lines of a trick learnt from daddylonglegs...

[alexrawnsley]

Beautiful. Thank you so much. The first one worked

[Cutter]

@ alexrawnsley

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.