+ Reply to Thread
Results 1 to 4 of 4

Sum and Count Consecutive High Values from Time Series Data

  1. #1
    Registered User
    Join Date
    04-17-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2016
    Posts
    33

    Sum and Count Consecutive High Values from Time Series Data

    Hello.
    I have two columns (D and G) of 5-minute interval records of solar electrical energy with associated timestamps in columns C and F. The min value is zero and the max value is 0.139 and there are no blank cells.

    I would like to get the total, from column G based on the following criteria:
    Aligned cells (same Row) in columns D and G must be >= 0.066.
    A minimum of two consecutive cells (in Column G) of >= 0.066 must start a summation block.
    Only one low value (<0.066) is allowed between two high (>=0.066) values, remembering that two high values must start off the block.
    Two consecutive low values (<0.066) indicate the end of a summation block, with the last high value (>=0.066) being the last value of the block to be summed.
    The attached file shows the following: From point 1 above, aligned cells (Cols C, D and F, G) of high values are couloured green.
    From point 2 above, using an =IF(AND(... formula, Column H flags (with a number 1) where two consecutive high value cells occur.
    Using an array formula, column I sums each block of consecutive high value cells (even if only one isolated high value is present: I don't want isolated high values included in the final sum, so Column J copies the sum in the adjacent cell from column I, only if two or more consecutive cells in Column G have high values.
    Finally, column L shows a selection of the desired results where a bold border encloses each summation block as well as the summed cells (the Sum formula is also visible).
    Note that not all the required data in column L is filled in since this is what I want to achieve, either with VBA (or formulae). The selection in column L should help explain the various patterns of Highs and Lows which are listed above. In summary, the following patterns of consecutive High and Low values are required to be summed:
    HH, HHH, HH(H or L)H, HH(H or L)H(H or L)H. Note that any amount of consecutive H values immediately following the last H in any of the four patterns is to be included in a summation block. Each summation block should begin with two Highs, and end on a High (which will be followed by two or more Lows).

    Hope I haven't over-explained this!
    A VBA or formulae solution would do if possible, My formulae columns can be discarded if a VBA solution exists. Thanks in advance.

    Edit: It would be great to have a count of how many records make up each summation block, if possible, but not essential.
    Attached Files Attached Files
    Last edited by GeoffH1; 03-01-2018 at 03:51 PM. Reason: Error with vale 0.006. Should read 0.066.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Sum and Count Consecutive High Values from Time Series Data

    I didn't really understand the detail of the patterns but an approach like the one below might be able to be adapted to work for you. It works by traversing the columns and noting the start and end row of the block. When the latter happens, it does the summation.

    Please Login or Register  to view this content.
    Martin

  3. #3
    Registered User
    Join Date
    04-17-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Sum and Count Consecutive High Values from Time Series Data

    Thanks mrice: apologies, I made two errors with a quoted number. The High values are >= 0.066 while the low values are <0.066 in all cases. Perhaps I can change your Low threshold to 0.066 and try it out. Will post back with results.

  4. #4
    Registered User
    Join Date
    04-17-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Sum and Count Consecutive High Values from Time Series Data

    mrice. Replacing 0.006 with 0.066 as LowThreshold, the overall result (61.26) looks to be in the region of what I am looking for, but I don't understand the individual summations in Column M, starting in (26 ,13) which reads 2.754. Can you explain what the 2.754 relates to?

    To explain the patterns a bit more. A High is a value >= 0.066. A Low is a value < 0.066. Two Highs followed by two Lows indicates that only the two Highs should be summed = HH i.e. only one Low is permitted between two Highs.

    HH(H or L)H means two Highs, followed by a High or one Low, then followed by a High, indicating that all four values are to be summed since only one Low value is found between two High values (of course, if more consecutive Highs appear at the end, then these Highs would also be summed). And, as long as a maximum of one low value surrounded by High values occurs, then the whole block can be summed (including the singular Low values).
    Last edited by GeoffH1; 03-02-2018 at 07:23 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return a Value in a Series of 3 or more Consecutive Values
    By Jonathan78 in forum Excel General
    Replies: 7
    Last Post: 05-16-2016, 04:45 PM
  2. [SOLVED] When was the last time a series was that high
    By h0ps in forum Excel General
    Replies: 5
    Last Post: 08-01-2014, 06:16 AM
  3. help cleaning up data and retrieving the time of day for high and low values...
    By yertleturtle in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-17-2014, 04:35 PM
  4. Length of the longest consecutive data series with values not null
    By jacknobody in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2013, 09:42 AM
  5. count the series of consecutive positive/negative values and sum them
    By otage in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-12-2012, 12:43 PM
  6. Replies: 4
    Last Post: 01-15-2012, 12:42 PM
  7. Loop To count a consecutive series
    By mthpsu in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-24-2011, 02:12 AM

Bookmarks

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