Find a highest consecutive range in a column

[EWek1]

If I have a column with 365 rows with each cell representing total sales for each day of the year. If I wanted to find any 30 consecutive days with the highest sum, how could I do that?

 

[Eric W]

Interesting. Try:

	A	B	C	D	E
1	Day	Sales		Day	30 Day Max
2	1	251		70	17191
3	2	931
4	3	295
5	4	566
6	5	10
7	6	677

<tbody>
</tbody>

Sheet9​

Array Formulas Cell Formula D2 {=MIN(IF(SUBTOTAL(9,OFFSET($B$2,ROW($B$2:$B$336)-ROW($B$2),0,30))=E2,$A$2:$A$336))} E2 {=MAX(SUBTOTAL(9,OFFSET($B$2,ROW($B$2:$B$336)-ROW($B$2),0,30)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>