CODEWITHSUNDEEP
excelexcel-formula
bramik
bramik

Reputation: 3

calculate current consecutive streak ignoring blanks

I have a daily log with four different value types. Positive, Negative, Zero, or Blank.

I am interested in the consecutive Zeroes, where Positive and Negative values end the streak, and Blanks are ignored.

Each record is on 1 row and contains a name and values

Example:

Day  1  2 3 4 5 6 7 8 
Fred 1 -2 0 _ 0 0 0 0  calculate current streak of 5
Jane 0 -5 _ 0 0 _ _ _  calculate current streak of 2
John 4 -6 3 0 0 0 3 0  calculate current streak of 1
Amie 2  0 0 0 0 0 0 -3 current streak 0

I can use COUNTIF to get the total zeroes, but not the streak

From this similar question, calculate consecute streak in excel row

I've tried variations of =IFERROR(MATCH(TRUE,INDEX(B2:I2<>0,0),0)-1,COUNT(B2:I2))

I'm getting wonky results, and my supervisor wants me to be done with this already. Any help or ideas would be greatly appreciated!

Upvotes: 0

Views: 435

Answers (1)

JvdV
JvdV

Reputation: 75990

enter image description here

Formula in K2:

=IFERROR(SUM(INDEX((COLUMN(B2:I2)>LOOKUP(2,1/((B2:I2<>0)*(B2:I2<>"")),COLUMN(B2:I2)))*(B2:I2=0)*(B2:I2<>""),)),COUNTIF(B2:I2,0))

Drag down..

There probably is a neater way.

Upvotes: 0

Related Questions