Assuming that your dates are in B3:Bnnn and they are in sequential
order, and the impression counts are in C3:Cnnn, you can use the
following formula:


=AVERAGE(OFFSET(B3,MATCH(TODAY(),B3:B1000,1)-
MIN(7,COUNT(B3:B1000)),1,MIN(7,COUNT(B3:B1000)),1) )

Change the "B1000" in the formula to a row number greater than the
total number of Date rows that you will ever have. This will average
the numbers in C1:Cnnn for dates that are in the 7 days (inclusive)
earlier than today. If the last value in B3:Bnnn is less than today,
the 7 days preceding (inclusive) that last day are averaged. E.g., if
the last cell is 11-May-2009, the 7 days preceding (inclusive) that
date are averaged. If there are less than 7 items, only those items
are averaged.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Mon, 25 May 2009 20:38:01 -0700, bbal20
wrote:

I am trying to calculate pacing based on the last 7 day average. My dataset
looks as follows:

Date Impressions
5/1/2009 4,562,077
5/2/2009 4,433,984
5/3/2009 4,340,952
5/4/2009 6,174,786
5/5/2009 6,736,687
5/6/2009 7,360,844
5/7/2009 6,576,612
5/8/2009 5,030,927
5/9/2009 3,702,990
5/10/2009 4,016,134
5/11/2009 5,894,572

Does anyone know how to construct a formula that always takes an average of
the last 7 days for the "impression" column based on the Now() date and how
many days are left in the month.