Calculating weekly average of a dynamic named range or pivot table data

[breakhappy]

Hello -

I have a table that keeps records of requests (no null values). I would like to display the weekly average number of requests. Currently, I have a pivot table running with the table sourced, and I get have the following formula for referencing the average number of requests.

=AVERAGE(GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","July","Date of request",DATE(2009,7,8)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","July","Date of request",DATE(2009,7,15)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","July","Date of request",DATE(2009,7,22)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","July","Date of request",DATE(2009,7,29)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","August","Date of request",DATE(2009,7,29)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","August","Date of request",DATE(2009,8,5)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","August","Date of request",DATE(2009,8,12)),GETPIVOTDATA("Organization",ProductDB_PivotTable!$A$3,"Month","August","Date of request",DATE(2009,8,26)))

The problem with this formula is that it doesn't expand as time goes on. I keep having to update it to include the next week as time progresses. Is there a better formula or method for doing this?

Also, I just converted all my columns of my table to dynamic named ranges. I know there's probably a way to =counta() the number of requests in the dynamic named ranges but how would I be able to tell excel to break up the records into weeks, then divide by the total for each week to get my weekly average?

Any help would be greatly appreciated. Thanks.