Consequtive total

[jbrain]

I'm looking for a forumla that will tell me the best 7 days of a series of
data. I have a SS of the past daily values (roughly 5 years) for my division
and need to know which 7 consequtive days were the greatest in total.

 

[Mike H]

Hi,

I'm sure there must be a better way because this is a bit long winded but it
does return the sum of the 7 highest consecutive values in the range a1- a100
which you can change to suit.

=SUMPRODUCT(MAX(A1:A100+OFFSET(A1:A100,1,0)+OFFSET(A1:A100,2,0)+OFFSET(A1:A100,3,0)+OFFSET(A1:A100,4,0)+OFFSET(A1:A100,5,0)+OFFSET(A1:A100,6,0)))

Mike

 

[T. Valko]

Try one of these:

Assume the range is A1:A100.

=SUMPRODUCT(MAX(A1:A94+A2:A95+A3:A96+A4:A97+A5:A98+A6:A99+A7:A100))

Or, this array formula** :

=MAX(SUBTOTAL(9,OFFSET(A1,ROW(A1:INDEX(A:A,ROWS(A1:A100)-7+1))-1,,7,)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)