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.

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

"jbrain" wrote:

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.

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)

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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

"jbrain" wrote:

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.