Trying to find the row number of the maximum total of 2 consecutive numbers in a column

[tdavo]

I am trying to find the maximum value of the sum of 2 consecutive numbers in a column of data.

I am using the following formula

=SUMPRODUCT(MAX((K6:K2984)*($F$6:$F$2984)+K5:K2983))

The data goes from K5 to K2984. Column F5 to F2984 has values of either 0 or 1. The aim is to find the maximum sum of 2 consecutive numbers only when there is a 1 in the F column.

The formula above works quite well and finds the highest consecutive number total. My problem is I want to identify the date and time when the max occurred.


The I column has dates for an entire month and the J column has times 15 minutes apart. (00:00, 00:15, 00:30 etc)

To do this I need to find the value in the "I" and J column when the max sum occurs.

So there would be 3 outputs: one for the maximum consecutive sum, one for the date and one for the time. The time of either the first or second component of the sum is ok.

Even identifying the row number where the maximum occurs would be sufficient.

I prefer to do this with a formula rather than a macro.

Does anyone have any suggestions?

 

[FranzV]

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:

1. Create a DateTime column by adding the time value to the date.
   1. =[@Date]+[@Time]
2. Find the row where the next reading is stored using another column.
   1. This step is the key to making the solution work even if the table is not sorted properly.
   2. =IFERROR(MATCH(MROUND([@DateTime]+15/(60*24),1/96),[DateTime],0),"")
   3. The MROUND is necessary to avoid errors due to Excel's decimal precision
   4. The +15/(60*24) will add 15 minutes to the DateTime value
3. Add a column with the value of the next reading if it has a 1 in column F.
   1. This step first multiplies the Binary and Values column and then returns the value corresponding to the row found in the previous step.
   2. =IFERROR(INDEX([Values]*[Binary],[@NextReadingsRow]),0)
   3. There is no need for an array formula because we are working inside a table.
4. Sum the value of the current row (times the binary value) plus the next reading's value.
   1. This step is pretty straightforward, just add the value retrieved during the previous step to the current value if column F equals 1.
   2. =IFERROR(INDEX([Values]*[Binary],[@NextReadingsRow]),0)
5. Find the maximum consecutive sum.
   1. A simple MAX() function will make due.
   2. =MAX(DummyTable[ConsecutiveSum])
6. Locate the row for the max consecutive sum.
   1. MATCH() comes to the rescue once again.
   2. =MATCH(J2,DummyTable[ConsecutiveSum],0)
   3. J2 refers to the max value previously calculated.
7. Retrieve the DateTime value for the row of the max consecutive sum.
   1. INDEX() can bring any value from the already found max row.
   2. I retrieved the DateTime value in a single cell, but you can get them separately if needed.
   3. =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.

1. Create Named Ranges for your Binary and Values columns to simplify formulas.
   1. Binary=$F$5:$F$2984
   2. Values=$K$5:$K$2984
2. Create a Named Range multiplying the Binary column times the Values column.
   1. BinaryTimesValues = Binary * Values
3. Shift the resulting array one row down.
   1. 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).
   2. ShiftedBinaryTimesValues = OFFSET( Binary, 1, 0 ) * OFFSET( Values, 1, 0 )
4. Add the arrays from steps 2. and 3.
   1. ConsecutiveSum = BinaryTimesValues + ShiftedBinaryTimesValues
   2. No science there.
5. Find the maximum value within the new array.
   1. A simple MAX() is needed.
   2. MaxConsecutiveSum = MAX( ConsecutiveSum )
6. Locate the row.
   1. MATCH() to the rescue.
   2. MaxRow =MATCH(MaxConsecutiveSum,ConsecutiveSum,0)
7. Retrieve the desired info.
   1. I retrieved DateTime, but you can get any column.
   2. 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>