Coincident Peak - Index Match Max and Offset?

[Billpete002]

Hey Everyone,

I am struggling with a formula - I know what I want to do, but I can't seem to wrap my head around how the formula is setup. I have daily data per each year per column (365 rows per column). I have used the following formula to generate a "4-day peak demand":

=MAX(SUMIF(OFFSET('Data Sheet'!B$2,ROW(INDIRECT("1:"&COUNTA('Data Sheet'!$B$2:$B$366)-4))-1,0,4),">0"))

I then used the following formula to find out what day/month this occurred in which used a set of helper columns:

=INDEX('Data Sheet'!$A$2:$A$366,MATCH(B2,'Data Sheet'!BD$5:$BD$366,0))

What this provides is the end date for the 4 summed days. However I want to also know what the maximum day is within that cumulative amount.

So the data looks something like this:

Date 1964
1/1 44
1/2 43
1/3 46
1/4 47
1/5 48
1/6 40
1/7 40

So I will have, from the example above, a cumulative 4-day peak of 184 and the date of 1/5. How do I write a formula to go back using the date to examine the rows and get an answer of 48? WARNING: I can't simply use the max function as a peak day may happen outside of this time period - I wish it were that easy!

Thanks in advance!

 

[barry houdini]

Assuming the formula to get the end date of the 4 day period is in G2 then try this formula to get the max in the 4 day period

=MAX(OFFSET('Data Sheet'!B$2,MATCH(G2,'Data Sheet'!$A$2:$A$366)-4,0,4,1))

 

[Billpete002]

Thanks Barry! I knew I was overthinking this. worked like a charm!