Formula to count number of consecutive values in previous cells

Hi Guys,

First post here. I'd like to find a formula (VBA is not an option in this case) to find the current "streak" of zero-defect weeks in a list of quality data. The data is organized into a fiscal week column and a quality defects column (0 being good).

How can I count, for the current fiscal week (last populated cell), how long the streak has been since the last quality defect? I don't want to add an extra column for running up a cumulative streak, I only have one cell available to me to do a streak calculation for the most recent week.

Any ideas??

XOR LX - I think that you're on the right track, but I'm getting a #N/A return from the cells that have a zero in the quality escapes column. Any idea what to adjust in the formula? Am I correct in that this formula will stand alone, i.e. it does not depend on incrementing a cumulative streak column?

Kelleytr, thanks for your solution, but I'm hoping to be able to perform this calculation in just one cell. I used the same exact formula to generate the sample spreadsheet

No, I'd like to count the number of consecutive zeros in col B, but only for the last FW. I only have one cell in which to do the calculation, I cannot add a new column to do a cumulative increment.

For example, the result for FW14 would be zero, because there were 6 quality escapes that week. However if only up through FW9 was populated, the result would be 3, becausee each of FW's 7, 8, and 9 are zeros.

That was it! Thanks!!!

Originally Posted by XOR LX

@kelleytr

Looks nice, only...you appear to be referencing a column D in your formula? Is this the Streak column given in the screenshot? If so, isn't that the very column which was given by the OP as their desired results and which we should be re-creating?

Regards

Yes, it does reference column D however it is utilizing the cell above it. As the formula goes down it is referencing a previous calculation above it within the same column.

Originally Posted by eschmidt12

XOR LX - I think that you're on the right track, but I'm getting a #N/A return from the cells that have a zero in the quality escapes column. Any idea what to adjust in the formula? Am I correct in that this formula will stand alone, i.e. it does not depend on incrementing a cumulative streak column?

Kelleytr, thanks for your solution, but I'm hoping to be able to perform this calculation in just one cell. I used the same exact formula to generate the sample spreadsheet

Are you sure? I didn't use the cumulative column and I believe you did. For my formula to work all you need is the quality escapes. The rest of it references itself within the column.

Assuming this isn't too many columns (fiscal weeks) it should be fine with an array formula. If it was over several thousand rows I would suggest my formula as an alternative.

Thanks Xor, I read his previous post also and I am not entirely sure what he means either. Since he said "That was it" earlier I believe he found what he was looking for.

Nice array formulas by the way. I'm still getting use to them but I know they can accomplish wonders!

Hey - sorry about the confusion...I was replying as fast as I could but I still wasn't as quick as you guys XOR LX, what I mean by "that was it" was that I had forgotten to enter your formula as an array. Once I entered it as an array, it worked exactly as I needed! Once I get back to work on Monday I'll try the other array formula that you suggested, it'll be great if that works.

kelleytr - yours is still a great solution (and is the one I used to generate the streak column), but does require an extra column. In my sample I had the cumulative column there just for reference. I had also used the >0 criteria as my "reset" for the incremental count for the streak.

I need to make this formula work as well. However if cell B2 is 0, I also get #N/A returned. I did enter the formula "=IF(B2>0,0,MATCH(TRUE,N(OFFSET(B2,-ROW(INDIRECT("1:"&ROWS($1:1))),,,))<>0,0))" as an array. I need some help.