Locate consecutive sequence of values & return column header name

Simon_L

New Member
Joined
Jul 29, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Background: I have used the following formula to calculate the highest sum of 3, 6 or 12 consecutive columns in my dataset
= SUMPRODUCT(MAX(B2:V2+C2:W2+D2:X2+E2:Y2+F2:Z2+G2:AA2)) # this example is for 6 consecutive columns and returns the result 15, See screen shot #1 below, under the column named Imax_6hr.

Question: I am wondering if I can find where those 6 values are in each row and then return the name of the column header for the last value in that set of 6? see screen shot #2. The result (column header name) in this example is 18.
The other way to get the result of 18 would be to a) locate the set of 6 consecutive values and b) count the number of cells between the last value (S2) back towards (B2) that would also return the result of 18.

FYI - The data is hourly rainfall data. The column header names are hours. The actual dataset has 96 columns x 1250 rows.

# screen shot 1
1596023650954.png

#screen shot 2
1596024111393.png



Appreciate any help!

Thanks,
Simon
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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