# Thread: number of consecutive days of value exceedance

1. I have daily data and would like to know how many consecutive days a value is equaled or exceeded during a particular period. In the following example for December, there would be 3 periods where the value equaled or exceeded 35: 12/2 at 1 day, 12/7 – 12/16 at 10 days, and 12/19 – 12/23 at 5 days.

12/1/09 29
12/2/09 35
12/3/09 29
12/4/09 29
12/5/09 22
12/6/09 30
12/7/09 36
12/8/09 36
12/9/09 36
12/10/09 36
12/11/09 36
12/12/09 36
12/13/09 36
12/14/09 36
12/15/09 36
12/16/09 36
12/17/09 33
12/18/09 34
12/19/09 42
12/20/09 42
12/21/09 42
12/22/09 42
12/23/09 42
12/24/09 27
12/25/09 24
12/26/09 28
12/27/09 30
12/28/09 29
12/29/09 24
12/30/09 28
12/31/09 30

On a yearly basis, I would like have the results as something like “17 periods >= 35; 2 periods of 20 days, 1 period of 18 days, 3 periods of 9 days, etc.” Knowing the date ranges would be nice, but not necessary.

Any ideas on how to parse out this data?
Thanks!

2. With the list in B2:B32, this gives the number of periods:

=ROUND(SUMPRODUCT(((B2:B32>35)<>(B3:B33>35))*1)/2;0)

3. Jan provided a means to get the number of periods, to get the details I think would require a macro. I can find some time to work on it, but it would be nice upfront to know exactly what you want the output to.

The text you would you like it in one huge text string or some table of values. Getting the date ranges is also possible but again, how do you want the presentation?

It could be a simple table of starting date/ ending date, # of days, even the average/ range in the data is possible, it could be sorted (descending) by number of days in the period if desired.

Steve

4. a table of start/end dates and number of days would be the output.

thanks and it's a low priority

5. Based on the data set you provided what should the output look like?

Steve

#### Posting Permissions

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