is it possible to create a rolling average in a pivot table?

[dave_f]

Hi All,

If I have a table of data like so:


Person Day Value
Joe 1/1/2010 100
Bob 1/1/2010 60
Jim 1/1/2010 75
Joe 1/2/2010 110
Bob 1/2/2010 61
Jim 1/2/2010 73
Joe 1/3/2010 130
Bob 1/3/2010 67
Jim 1/3/2010 95
...etc.

I'd like to create a pivot table that will give me a rolling weekly average by the dates, but still be able to filter on the person name. So in the pivot table for date 1/7/2010 it should show the average of 1/1/2010 - 1/7/2010.

Can anyone tell me if this is possible, and if so, how? My actual example has many more fields than this so creating the rolling averages inside the data table would be a pain and I'd like to avoid it if possible.


thanks in advance for any help you can provide.

Dave