Indicate the Instance of Consecutive Zeros in a Row

Hello,

I am trying to figure out a formula where an indicator of 1 is put at the end of a row that contains an instance of seven consecutive zeros in that same row. Also the number of times there are seven consecutive zeros does not matter, if there is one instance then add a 1 indicator, if there are 5 instances then a 1 indicator is still fine.

So in the sample I have attached, rows 3 and 6 would receive a 1 indicator, and the rest would receive nothing or a 0 is fine as well.

Thanks for any help.

excel_help_sample.xlsx

EDIT: I have this so far. This is a modified version of a formula I found else where on the forum that pretty much works for what I'm trying to accomplish.

Please Login or Register  to view this content.

However, I'm not 100% such exactly what its doing, so an explanation would be great

If the formula works then use it. I understand the curiosity factor, though. It is difficult to put into words, so first understand what each function does (e.g. INDIRECT and OFFSET) and then try Formulas->Evaluate Formula and step through each calculation and see if you can follow each step. Be aware that using the SUMPRODUCT function pushes some of the other functions to act upon arrays, so you will likely see something like this {0,1,2,3,4}.