Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    With the list in B2:B32, this gives the number of periods:

    =ROUND(SUMPRODUCT(((B2:B32>35)<>(B3:B33>35))*1)/2;0)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    a table of start/end dates and number of days would be the output.

    thanks and it's a low priority

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
  •