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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,013
Messages
5,834,925
Members
430,326
Latest member
tomwax46

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