Formula for rolling averages

[Andrew.yvr]

In the example below, the red box is a drop down list so that you can select a specific month & year.

I would like a formula that will calculate a rolling average for a total of 12 months including the date in the red box.

In this example, if the date selected is Jun-06, I would like to know the average from Jul-05 to Jun-06.

Does anyone know how to do this?

<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 2976" width=93><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: red 2pt double; BORDER-TOP: red 2pt double; BORDER-LEFT: red 2pt double; WIDTH: 70pt; BORDER-BOTTOM: red 2pt double; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=93 height=17>Jun-06</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Month</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Green</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Jan-05</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>2.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Feb-05</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Mar-05</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Apr-05</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>5.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>May-05</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>6.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Jun-05</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>7.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Jul-05</TD><TD class=xl77 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Aug-05</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>6.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Sep-05</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>7.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Oct-05</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>8.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Nov-05</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>9.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Dec-05</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>10.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Jan-06</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Feb-06</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>6.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Mar-06</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>7.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Apr-06</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>8.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>May-06</TD><TD class=xl78 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>9.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Jun-06</TD><TD class=xl79 style="BORDER-RIGHT: red 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: red 0.5pt solid; BORDER-BOTTOM: red 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>10.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Jul-06</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>2.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Aug-06</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Sep-06</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Oct-06</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>5.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Nov-06</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>6.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>Dec-06</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>7.0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Roll Avg.</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Thanks!

Andrew

 

[Magriza]

I don't know what ranges your data sits in but yes, this would be possible by using an OFFSET.
=AVERAGE(OFFSET("Green cell",MATCH("date","date range",0),0,-12,1)

 

[Andrew.yvr]

Thanks Magriza - it works!

Andrew

 

[delaneyjm]

In Excel 2007, you could do the following:

=AVERAGEIFS(B1:B100,A1:A100,"<"&C1,A1:A100,">"&DATE(YEAR(C1)-1,MONTH(c1),DAY(c1)))

where B1:B100 is the range you want to average, A1:A100 are the months, and C1 is the cell with the date.

If you need a pre-2007 formula, the formula posted by Magriza will work.

 

[EB08]

This may or may not come into play for you as you may be setting your validation to only allow dates 12 months or greater into your data set; but if not, i threw a little piece in the previously stated formula that will adjust the average to look at less than 12 months if the selected month is less than 12 months into the year (i.e. 8 months into data set would give you average of 8 months).

Same layout as you've posted, data starting in A1 (where you select month).

=AVERAGE(OFFSET(B3,MATCH(A1,A4:A27,0),,MAX(ROW(B4)-(MATCH(A1,A4:A27,0)+ROW(B4)),-12),1))