# Count the maximum consecutive TRUEs

1. ## Count the maximum consecutive TRUEs

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.

2. ## Re: Count the maximum consecutive TRUEs

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.

3. ## Re: Count the maximum consecutive TRUEs

Originally Posted by 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.
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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1