Rolling 4 Week Average

[stanleytheyak]

I've seen some other posts on this, but I can't seem to get them to work in my situation. I have the set of data below:

<table border="1" cellpadding="0" cellspacing="0"><tbody><tr style="height: 52px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">Week</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">Week Start</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">Week End</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">Avg Chat Session Duration</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">1</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">12/26/2010</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/1/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">2</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/2/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/8/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:08:09</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">3</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/9/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/15/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:08:49</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">4</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/16/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/22/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:09:35</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">5</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/23/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/29/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:09:15</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">6</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">1/30/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/5/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:08:56</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">7</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/6/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/12/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:09:28</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">8</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/13/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/19/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:08:46</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">9</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/20/2011</td> <td style="text-align: left; font-family: Tahoma; font-size: 8pt;">2/26/2011</td> <td style="text-align: center; font-family: Tahoma; font-size: 8pt;">0:08:33</td></tr></tbody></table>
What I'm looking to do is get a rolling four week agerage of the Average Chat Session Duration (I know, average of an average sucks, but it's the data I'm stuck with.) I'd like to put it in the format below:

<table border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"><td>A</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">48</td> <td> </td> <td style="text-align: right;">1/2/2011</td> <td style="text-align: right;">1/9/2011</td> <td style="text-align: right; color: rgb(255, 0, 0);">1/16/2011</td> <td style="text-align: right;">1/23/2011</td> <td style="text-align: right;">1/30/2011</td> <td style="text-align: right;">2/6/2011</td> <td style="text-align: right;">2/13/2011</td> <td style="text-align: right; color: rgb(255, 0, 0);">2/20/2011</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">49</td> <td> </td> <td style="text-align: right;">2</td> <td style="text-align: right;">3</td> <td style="text-align: right; color: rgb(255, 0, 0);">4</td> <td style="text-align: right;">5</td> <td style="text-align: right;">6</td> <td style="text-align: right;">7</td> <td style="text-align: right;">8</td> <td style="text-align: right; color: rgb(255, 0, 0);">9</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">55</td> <td style="text-align: left; background-color: rgb(192, 192, 192); font-weight: bold;">AHT Forecast</td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); color: rgb(255, 0, 0); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td> <td style="background-color: rgb(192, 192, 192); font-weight: bold;"> </td></tr></tbody></table>
I'd like it to look at the week start date (row 48 above) and look at the previous 4 weeks. So, if we're looking at H55, it would pull the average of D3:D6 in the data table at the top of the post.

As always, your suggestions are greatly appreciated.

 

[Darren Bartrup]

In cell H55 (and drag across to the other cells). You don't need to number the weeks - it looks for the date and references the 4 rows above it.

=AVERAGE(OFFSET($B$1,MATCH(H$48,$B$2:$B$10,0)-1,2,-4))

You'll get #REF! errors in the first three months as it's trying to reference rows below 1.

I've assumed your data table is in cell A1:D10.