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.
Bookmarks