Count number of cells since last value greater than a value

**justibm** · 11-02-2018, 12:37 PM

I am trying to create a column that will count the number of cells in another column since the last cell that had a value greater than .19. In other words, for each cell in column B to count up the number of cells in column A from the same row that were less than .19 and then stop counting once it reaches a number greater than .19.

**xladept** · 11-02-2018, 01:14 PM

Maybe:

Formula:

Please Login or Register  to view this content.

**MrShorty** · 11-02-2018, 01:27 PM

I interpreted your explanation a little different than xladept. I would do something like (assuming your "signal" data starts in A1):
B1 =IF(A1<0.19,1,0) -- to initialize the tracking column.
B2 =IF(A2<0.19,B1+1,0) copy down.

You don't say what you want to do when column A exactly equals 0.19. My solution includes exactly 0.19 in the greater than 0.19 camp -- adjust formulas for your desired behavior for exactly 0.19.

You don't way what you want to do with those counts. I could see =MAX(B:B) to get the length of the longest streak of values below 0.19.

xladept's solution simply counts all of the entries in A that are less than 0.19. Mine counts "streaks" where the values are below 0.19. Choice depends on exactly what you want do for these counts.