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

tdavo

New Member
Joined
Feb 9, 2014
Messages
2
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?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
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.


FGHIJKLMNO
1MaxConsecutiveSumMaxRowMaxDateTimeMaxConsecutiveSumMaxRowMaxDateTime
2 1,971,76763507-01 14:30 1,971,76763507-01 14:30
3
4BinaryGHDateTimeValuesDateTimeNextReadingsRowNextReadingsValueConsecutiveSum
5101/01/20170:00 377,546 42,736.00 2 237,999 615,545
6101/01/20170:15 237,999 42,736.01 3 756,770 994,769
7101/01/20170:30 756,770 42,736.02 4 - 756,770
8001/01/20170:45 144,700 42,736.03 5 - -
9001/01/20171:00 285,027 42,736.04 6 475,129 475,129
10101/01/20171:15 475,129 42,736.05 7 - 475,129
11001/01/20171:30 392,395 42,736.06 8 591,862 591,862
12101/01/20171:45 591,862 42,736.07 9 344,395 936,257

<tbody>
</tbody>

Worksheet Formulas
CellFormula
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
NameRefers 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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,215
Messages
5,594,883
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top