I know of two ways to do this. The first needs to add helper columns to your table, the second relies on Named Ranges.
I created a dummy table and applied table format using
CTRL+T. I strongly suggest you format your data this way to make formulas easier to read and write.
The helper columns approach follows these steps:
- Create a DateTime column by adding the time value to the date.
- =[@Date]+[@Time]
- Find the row where the next reading is stored using another column.
- This step is the key to making the solution work even if the table is not sorted properly.
- =IFERROR(MATCH(MROUND([@DateTime]+15/(60*24),1/96),[DateTime],0),"")
- The MROUND is necessary to avoid errors due to Excel's decimal precision
- The +15/(60*24) will add 15 minutes to the DateTime value
- Add a column with the value of the next reading if it has a 1 in column F.
- This step first multiplies the Binary and Values column and then returns the value corresponding to the row found in the previous step.
- =IFERROR(INDEX([Values]*[Binary],[@NextReadingsRow]),0)
- There is no need for an array formula because we are working inside a table.
- Sum the value of the current row (times the binary value) plus the next reading's value.
- This step is pretty straightforward, just add the value retrieved during the previous step to the current value if column F equals 1.
- =IFERROR(INDEX([Values]*[Binary],[@NextReadingsRow]),0)
- Find the maximum consecutive sum.
- A simple MAX() function will make due.
- =MAX(DummyTable[ConsecutiveSum])
- Locate the row for the max consecutive sum.
- MATCH() comes to the rescue once again.
- =MATCH(J2,DummyTable[ConsecutiveSum],0)
- J2 refers to the max value previously calculated.
- Retrieve the DateTime value for the row of the max consecutive sum.
- INDEX() can bring any value from the already found max row.
- I retrieved the DateTime value in a single cell, but you can get them separately if needed.
- =INDEX(DummyTable[DateTime],K2)
The Named Ranges approach will only work with a sorted table, but requires only a single cell to get the row you are looking for.
- Create Named Ranges for your Binary and Values columns to simplify formulas.
- Binary=$F$5:$F$2984
- Values=$K$5:$K$2984
- Create a Named Range multiplying the Binary column times the Values column.
- BinaryTimesValues = Binary * Values
- Shift the resulting array one row down.
- You need to OFFSET() each array before multiplying them, because once you perform an operation the arrays stops referencing a range and turns into values (OFFSET needs a range in the first argument).
- ShiftedBinaryTimesValues = OFFSET( Binary, 1, 0 ) * OFFSET( Values, 1, 0 )
- Add the arrays from steps 2. and 3.
- ConsecutiveSum = BinaryTimesValues + ShiftedBinaryTimesValues
- No science there.
- Find the maximum value within the new array.
- A simple MAX() is needed.
- MaxConsecutiveSum = MAX( ConsecutiveSum )
- Locate the row.
- MATCH() to the rescue.
- MaxRow =MATCH(MaxConsecutiveSum,ConsecutiveSum,0)
- Retrieve the desired info.
- I retrieved DateTime, but you can get any column.
- MaxDateTime = INDEX(DateTime,MaxRow)
I hope my explanation is not confusing and helps you solve the issue.
| F | G | H | I | J | K | L | M | N | O |
---|
1 | | | | | MaxConsecutiveSum | MaxRow | MaxDateTime | MaxConsecutiveSum | MaxRow | MaxDateTime |
2 | | | | | 1,971,767 | 635 | 07-01 14:30 | 1,971,767 | 635 | 07-01 14:30 |
3 | | | | | | | | | | |
4 | Binary | G | H | Date | Time | Values | DateTime | NextReadingsRow | NextReadingsValue | ConsecutiveSum |
5 | 1 | | | 01/01/2017 | 0:00 | 377,546 | 42,736.00 | 2 | 237,999 | 615,545 |
6 | 1 | | | 01/01/2017 | 0:15 | 237,999 | 42,736.01 | 3 | 756,770 | 994,769 |
7 | 1 | | | 01/01/2017 | 0:30 | 756,770 | 42,736.02 | 4 | - | 756,770 |
8 | 0 | | | 01/01/2017 | 0:45 | 144,700 | 42,736.03 | 5 | - | - |
9 | 0 | | | 01/01/2017 | 1:00 | 285,027 | 42,736.04 | 6 | 475,129 | 475,129 |
10 | 1 | | | 01/01/2017 | 1:15 | 475,129 | 42,736.05 | 7 | - | 475,129 |
11 | 0 | | | 01/01/2017 | 1:30 | 392,395 | 42,736.06 | 8 | 591,862 | 591,862 |
12 | 1 | | | 01/01/2017 | 1:45 | 591,862 | 42,736.07 | 9 | 344,395 | 936,257 |
<tbody>
</tbody>
Worksheet Formulas
Cell | Formula |
---|
J2 | =MAX(DummyTable[ConsecutiveSum]) |
---|
K2 | =MATCH(J2,DummyTable[ConsecutiveSum],0) |
---|
L2 | =INDEX(DummyTable[DateTime],K2) |
---|
L5 | =[@Date]+[@Time] |
---|
M5 | =IFERROR(MATCH(MROUND([@DateTime]+15/(60*24),1/96),[DateTime],0),"") |
---|
N5 | =IFERROR(INDEX([Values]*[Binary],[@NextReadingsRow]),0) |
---|
O5 | =[@Binary]*[@Values]+[@NextReadingsValue] |
---|
<tbody>
</tbody> |
<tbody>
</tbody>
Workbook Defined Names
Name | Refers To |
---|
Binary | =DummyTable[Binary] |
---|
BinaryTimesValues | =Binary*Values |
---|
ConsecutiveSum | =BinaryTimesValues+ShiftedBinaryTimesValues |
---|
DateTime | =DummyTable[DateTime] |
---|
MaxConsecutiveSum | =MAX(ConsecutiveSum) |
---|
MaxDateTime | =INDEX(DateTime,MaxRow) |
---|
MaxRow | =MATCH(MaxConsecutiveSum,ConsecutiveSum,0) |
---|
ShiftedBinaryTimesValues | =OFFSET(Binary,1,0)*OFFSET(Values,1,0) |
---|
Values | =DummyTable[Values] |
---|
<tbody>
</tbody> |
<tbody>
</tbody>