Count the maximum consecutive TRUEs

[tomgilb]

Could someone please help me with 2 worksheet formulas?:

1) I need to count the maximum consecutive TRUEs in a single column, in a descending-order worksheet that cannot be sorted in ascending order, for n rows beginning in row 3, using Excel 2000, where n is user defined.

2) I need to count the maximum consecutive TRUEs in a single column, in a descending-order worksheet that cannot be sorted in ascending order, for n minutes based on a timestamp in another column, beginning in row 3, using Excel 2000, where n is user defined.

I've already calculated the total TRUEs by n rows and n minutes, but I need to also count the max consecutive TRUEs, and I'm completely stumped.

Attached is a workbook with what I've already done.

[MarvinP]

Hi tomgilb and welcome to the forum,

See if the attached helps. I've used Column D for the helper column with the answer in D1.

[tomgilb]

Hi tomgilb and welcome to the forum,

See if the attached helps. I've used Column D for the helper column with the answer in D1.

Thanks Marvin,

The formula =IF(B3=TRUE,D2+1,0) you offered increments the value, but it doesn't count the consecutive TRUEs n rows back (e.g. 6 rows, row 3 thru row 8). It also increments in ascending order, and the timestamps (worksheet) are in descending order.

I've tried this in cell D3:
=IF(AND(B3=TRUE,D4<$D$1),B3+D4,0)
It increments the consecutive TRUE count within the variable length n but fails to maintain the count for n rows back. This is my starting formula, and I've tried many iterations without success. This one is a brain twister for me.