Consequtive total

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jbrain - 27 May 2008 19:31 GMT

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.

Reply to this Message

Mike H - 27 May 2008 19:48 GMT

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

> 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.

Reply to this Message

T. Valko - 27 May 2008 21:52 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Hi,
>
[quoted text clipped - 13 lines]
>> division
>> and need to know which 7 consequtive days were the greatest in total.

Reply to this Message