Need help with "Rolling" Average

Hi Everybody

I've got a "Date" Column which has every day of every month, and next to that several columns with different types of data to correspond to a particular date.

To simplify this (because there are TONS of dates), I've made another "Date" column that displays only every 5th day. Now what I'd like to do of course is average the corresponding data over five days.

I don't know how to write a single formula that I can copy down that will do this.

I've attached a sample spreadsheet with only one month's data, the stuff I need a formula / code for is in red

Riley_5000

I think to make your life easier and to avoid need for Array formulae I'd be inclined myself to have a dummy row setup in table preceding first real record that reads:

A2: =MIN(G:G)-5

I'm also not sure what the current row 2 is meant to represent given no date is assigned to it...

Assuming you've inserted A2 as suggested then you can simply use INDEX to create range to Average given you have 1 record per day

Please Login or Register  to view this content.

The above would be preferable to running an AVERAGE(IF array setup... if you have XL2007 you could look to use AVERAGEIFS if you wish but you will still need to adopt an approach along the lines of the above given your data ranges are not always going to be 5 days (some will be 6)... I'm also not sure whether the way you're handling Feb will give you accurate YOY recordings.