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.
See if the attached helps. I've used Column D for the helper column with the answer in D1.
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:
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.