Need to Average highest continuous 30 daily values over past 48 month span

Can you provide a mock file with example data so we can see your layout please. Make sure the data does not contain anything sensitive.
Also make sure you provide enough data for us to work with (you want highest 30 values so provide 50ish please) and also include the result expected for this data manually entered (if you can highlight in a colour to stand out so we can see clearly where the results are). To upload a file click Go Advanced then select Manage Attachments.

Will the period you are checking over always be the last 48 months or is it possible that this will vary?

I don't think there is a single formula that can calculate that for you but you can use helper calculations.. Assuming your data are in column A you can use column B to calculate the 30 day rolling average. You can then use Max on column B to find your highest 30 day period.

if you can upload some sample data I'll set it up for you..

Assuming A1 is a header, and there are 1060 rows of data below,

=MAX(SUBTOTAL(9, OFFSET(A1, ROW(INDIRECT("1:1060")), 0, 30, 1)))

The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

I make the solution 16.33786 on the 19/03/2010


example (11).xlsx

In D2,

=MAX(SUBTOTAL(9, OFFSET(B1, ROW(INDIRECT("1:1060")), 0, 30, 1)))/30

... confirmed with Ctrl+Shift+Enter

@ Andy: Your formula is only averaging 29 days.

Thank you for the correction

Originally Posted by shg

=MAX(SUBTOTAL(9, OFFSET(B1, ROW(INDIRECT("1:1060")), 0, 30, 1)))/30

I think you probably need to extend the range to get the full 4 years......but given shg's formula in D2 to get the high average you can use this formula for the start date of that 30 day period

=INDEX(A2:A1462,MATCH(D2*30,SUBTOTAL(9,OFFSET(B1, ROW(INDIRECT("1:1461")),0,30)),0))

Confirmed with CTRL+SHIFT+ENTER and formatted as a date

I get 17th Feb 2010

example (11) (1).xlsx

. Sorry but I disagree... I've charted the data which points to mid March with a 30 day max average of 16.255.

I think it's the same thing really, Andy - the start date of the 30 day period is 17th Feb 2010 - so the end date is mid-March, 18th to be precise

Because you are using MROUND that will make my formula for the date give an error but based on your value in C1 you can still do it with

=INDEX(A2:A1493,MATCH(C1,MROUND(SUBTOTAL(9,OFFSET(B1, ROW(INDIRECT("1:1492")),0,30))/30,0.001),0))

That will give 17th Feb, i.e. the start date of the 30 day period with the greatest combined value, clearly you could add 29 to get the end date - that would match your results

Damn - LOL......... I bow to your superiority

In practice, I'd be inclined to use Andy's approach -- it's a lot lighter weight, and non-volatile.

The range of cells of the highest 30 day average is from Feb 17,2010 to Mar 18, 2010 inclusive. The average for that range is 16.25491

My methodology was not elegant like the above formulae but it was effective.

I entered in cell C30 =AVERAGE(B1:B30) and copied this down the length of the data to give a running AVERAGE of 30 cells in column B. I then in D30 entered =RANK(C30,$C$30:$C$1060)

I found number 1 in column D and that was the end of the range of highest average.

That's a workable solution, good job.

Being entirely self-taught, sometimes solutions come the "long way around".

Thanks

Me too ......................There is usually one perfect solution to a problem and about 10 different workarounds... To be perfectly honest, as far as i'm concerned anything that works is good

Glad to hear it Bill..........
Can you mark the thread as solved mate ... It's at the top of the thread under the thread tools menu