7 Day Pacing Formula Help

Okay. I'm posting this for a second time in hopes that I can get some help. I
am trying to right a formula that gives me a pacing number for the end of the
current month based on the actuall impressions delivered already and an
average of the last 7 days to determine what I will end the month off with.
The "date" coulmn is B and the "Impressions" column is C. The dataset I am
working with is structured like below:

Date Impressions
5/1/2009 5,997,615
5/2/2009 5,338,693
5/3/2009 5,453,509
5/4/2009 7,112,618
5/5/2009 8,474,951
5/6/2009 8,131,324
5/7/2009 7,277,283
5/8/2009 6,335,335
5/9/2009 5,523,790
5/10/2009 5,453,481
5/11/2009 6,752,749
5/12/2009 6,548,278
5/13/2009 6,215,908
5/14/2009 6,507,920
5/15/2009 6,349,852
5/16/2009 6,166,696
5/17/2009 6,151,703
5/18/2009 5,662,645
5/19/2009 6,733,170
5/20/2009 5,453,514
5/21/2009 5,231,829
5/22/2009 5,344,059
5/23/2009 5,890,980
5/24/2009 6,224,008
5/25/2009 6,774,193
5/26/2009 6,553,692
5/27/2009 6,905,603
5/28/2009 6,384,275
5/29/2009 2,976,088
5/30/2009 2,157,571
5/31/2009 0

1st, I am trying to determine what the average is for "Impressions" for the
last 7 days based on the Today() date and the last preceding 7 days. Once I
determine the average of the last 7 days I want to apply that average to
every day in the future including the current date. for example, if today was
May 25th, I want the average of "Impressions" from the 18th through the 24th.
I then want to apply that average to every day starting from the 25th to the
31st. Lastly, the daily average should be applied to the sum of everything
the was actually delivered or that I have data populated for. This should
give me an overall 7 day pacing value for "Impressions" for the end of the
month as days are completed. That was a bit wordy I know. Here is the formula
I've written so far, but it isn't giving me the correct value.

=AVERAGE(OFFSET($B2,MATCH(TODAY(),$B$2:$B$32,1)-7,1,7,1))+SUM($C$2:$C$32)

--
Thank you for your help and support

Tryout the attached.

Here is how I would get the average of the last(most recent 7 days) 7
days(not based on today's date).

Note: There are no worksheet formulas.

Dennis

"bbal20" wrote in message
...
Okay. I'm posting this for a second time in hopes that I can get some
help. I
am trying to right a formula that gives me a pacing number for the end of
the
current month based on the actuall impressions delivered already and an
average of the last 7 days to determine what I will end the month off
with.
The "date" coulmn is B and the "Impressions" column is C. The dataset I am
working with is structured like below:

Date Impressions
5/1/2009 5,997,615
5/2/2009 5,338,693
5/3/2009 5,453,509
5/4/2009 7,112,618
5/5/2009 8,474,951
5/6/2009 8,131,324
5/7/2009 7,277,283
5/8/2009 6,335,335
5/9/2009 5,523,790
5/10/2009 5,453,481
5/11/2009 6,752,749
5/12/2009 6,548,278
5/13/2009 6,215,908
5/14/2009 6,507,920
5/15/2009 6,349,852
5/16/2009 6,166,696
5/17/2009 6,151,703
5/18/2009 5,662,645
5/19/2009 6,733,170
5/20/2009 5,453,514
5/21/2009 5,231,829
5/22/2009 5,344,059
5/23/2009 5,890,980
5/24/2009 6,224,008
5/25/2009 6,774,193
5/26/2009 6,553,692
5/27/2009 6,905,603
5/28/2009 6,384,275
5/29/2009 2,976,088
5/30/2009 2,157,571
5/31/2009 0

1st, I am trying to determine what the average is for "Impressions" for
the
last 7 days based on the Today() date and the last preceding 7 days. Once
I
determine the average of the last 7 days I want to apply that average to
every day in the future including the current date. for example, if today
was
May 25th, I want the average of "Impressions" from the 18th through the
24th.
I then want to apply that average to every day starting from the 25th to
the
31st. Lastly, the daily average should be applied to the sum of everything
the was actually delivered or that I have data populated for. This should
give me an overall 7 day pacing value for "Impressions" for the end of the
month as days are completed. That was a bit wordy I know. Here is the
formula
I've written so far, but it isn't giving me the correct value.

=AVERAGE(OFFSET($B2,MATCH(TODAY(),$B$2:$B$32,1)-7,1,7,1))+SUM($C$2:$C$32)

--
Thank you for your help and support

Hey Dennis,

Did you post a formula or something? I don't see anything.
--
Thank you for your help and support


"Dennis Tucker" wrote:

Tryout the attached.

Here is how I would get the average of the last(most recent 7 days) 7
days(not based on today's date).

Note: There are no worksheet formulas.

Dennis

"bbal20" wrote in message
...
Okay. I'm posting this for a second time in hopes that I can get some
help. I
am trying to right a formula that gives me a pacing number for the end of
the
current month based on the actuall impressions delivered already and an
average of the last 7 days to determine what I will end the month off
with.
The "date" coulmn is B and the "Impressions" column is C. The dataset I am
working with is structured like below:

Date Impressions
5/1/2009 5,997,615
5/2/2009 5,338,693
5/3/2009 5,453,509
5/4/2009 7,112,618
5/5/2009 8,474,951
5/6/2009 8,131,324
5/7/2009 7,277,283
5/8/2009 6,335,335
5/9/2009 5,523,790
5/10/2009 5,453,481
5/11/2009 6,752,749
5/12/2009 6,548,278
5/13/2009 6,215,908
5/14/2009 6,507,920
5/15/2009 6,349,852
5/16/2009 6,166,696
5/17/2009 6,151,703
5/18/2009 5,662,645
5/19/2009 6,733,170
5/20/2009 5,453,514
5/21/2009 5,231,829
5/22/2009 5,344,059
5/23/2009 5,890,980
5/24/2009 6,224,008
5/25/2009 6,774,193
5/26/2009 6,553,692
5/27/2009 6,905,603
5/28/2009 6,384,275
5/29/2009 2,976,088
5/30/2009 2,157,571
5/31/2009 0

1st, I am trying to determine what the average is for "Impressions" for
the
last 7 days based on the Today() date and the last preceding 7 days. Once
I
determine the average of the last 7 days I want to apply that average to
every day in the future including the current date. for example, if today
was
May 25th, I want the average of "Impressions" from the 18th through the
24th.
I then want to apply that average to every day starting from the 25th to
the
31st. Lastly, the daily average should be applied to the sum of everything
the was actually delivered or that I have data populated for. This should
give me an overall 7 day pacing value for "Impressions" for the end of the
month as days are completed. That was a bit wordy I know. Here is the
formula
I've written so far, but it isn't giving me the correct value.

=AVERAGE(OFFSET($B2,MATCH(TODAY(),$B$2:$B$32,1)-7,1,7,1))+SUM($C$2:$C$32)

--
Thank you for your help and support

I posted Sample.xls

Dennis

"bbal20" wrote in message
...
Hey Dennis,

Did you post a formula or something? I don't see anything.
--
Thank you for your help and support


"Dennis Tucker" wrote:

Tryout the attached.

Here is how I would get the average of the last(most recent 7 days) 7
days(not based on today's date).

Note: There are no worksheet formulas.

Dennis

"bbal20" wrote in message
...
Okay. I'm posting this for a second time in hopes that I can get some
help. I
am trying to right a formula that gives me a pacing number for the end
of
the
current month based on the actuall impressions delivered already and an
average of the last 7 days to determine what I will end the month off
with.
The "date" coulmn is B and the "Impressions" column is C. The dataset I
am
working with is structured like below:

Date Impressions
5/1/2009 5,997,615
5/2/2009 5,338,693
5/3/2009 5,453,509
5/4/2009 7,112,618
5/5/2009 8,474,951
5/6/2009 8,131,324
5/7/2009 7,277,283
5/8/2009 6,335,335
5/9/2009 5,523,790
5/10/2009 5,453,481
5/11/2009 6,752,749
5/12/2009 6,548,278
5/13/2009 6,215,908
5/14/2009 6,507,920
5/15/2009 6,349,852
5/16/2009 6,166,696
5/17/2009 6,151,703
5/18/2009 5,662,645
5/19/2009 6,733,170
5/20/2009 5,453,514
5/21/2009 5,231,829
5/22/2009 5,344,059
5/23/2009 5,890,980
5/24/2009 6,224,008
5/25/2009 6,774,193
5/26/2009 6,553,692
5/27/2009 6,905,603
5/28/2009 6,384,275
5/29/2009 2,976,088
5/30/2009 2,157,571
5/31/2009 0

1st, I am trying to determine what the average is for "Impressions" for
the
last 7 days based on the Today() date and the last preceding 7 days.
Once
I
determine the average of the last 7 days I want to apply that average
to
every day in the future including the current date. for example, if
today
was
May 25th, I want the average of "Impressions" from the 18th through the
24th.
I then want to apply that average to every day starting from the 25th
to
the
31st. Lastly, the daily average should be applied to the sum of
everything
the was actually delivered or that I have data populated for. This
should
give me an overall 7 day pacing value for "Impressions" for the end of
the
month as days are completed. That was a bit wordy I know. Here is the
formula
I've written so far, but it isn't giving me the correct value.

=AVERAGE(OFFSET($B2,MATCH(TODAY(),$B$2:$B$32,1)-7,1,7,1))+SUM($C$2:$C$32)

--
Thank you for your help and support

Here it is again. It contains your sample data and my macro.

Dennis

"Dennis Tucker" wrote in message
...
I posted Sample.xls

Dennis

"bbal20" wrote in message
...
Hey Dennis,

Did you post a formula or something? I don't see anything.
--
Thank you for your help and support


"Dennis Tucker" wrote:

Tryout the attached.

Here is how I would get the average of the last(most recent 7 days) 7
days(not based on today's date).

Note: There are no worksheet formulas.

Dennis

"bbal20" wrote in message
...
Okay. I'm posting this for a second time in hopes that I can get some
help. I
am trying to right a formula that gives me a pacing number for the end
of
the
current month based on the actuall impressions delivered already and
an
average of the last 7 days to determine what I will end the month off
with.
The "date" coulmn is B and the "Impressions" column is C. The dataset
I am
working with is structured like below:

Date Impressions
5/1/2009 5,997,615
5/2/2009 5,338,693
5/3/2009 5,453,509
5/4/2009 7,112,618
5/5/2009 8,474,951
5/6/2009 8,131,324
5/7/2009 7,277,283
5/8/2009 6,335,335
5/9/2009 5,523,790
5/10/2009 5,453,481
5/11/2009 6,752,749
5/12/2009 6,548,278
5/13/2009 6,215,908
5/14/2009 6,507,920
5/15/2009 6,349,852
5/16/2009 6,166,696
5/17/2009 6,151,703
5/18/2009 5,662,645
5/19/2009 6,733,170
5/20/2009 5,453,514
5/21/2009 5,231,829
5/22/2009 5,344,059
5/23/2009 5,890,980
5/24/2009 6,224,008
5/25/2009 6,774,193
5/26/2009 6,553,692
5/27/2009 6,905,603
5/28/2009 6,384,275
5/29/2009 2,976,088
5/30/2009 2,157,571
5/31/2009 0

1st, I am trying to determine what the average is for "Impressions"
for
the
last 7 days based on the Today() date and the last preceding 7 days.
Once
I
determine the average of the last 7 days I want to apply that average
to
every day in the future including the current date. for example, if
today
was
May 25th, I want the average of "Impressions" from the 18th through
the
24th.
I then want to apply that average to every day starting from the 25th
to
the
31st. Lastly, the daily average should be applied to the sum of
everything
the was actually delivered or that I have data populated for. This
should
give me an overall 7 day pacing value for "Impressions" for the end of
the
month as days are completed. That was a bit wordy I know. Here is the
formula
I've written so far, but it isn't giving me the correct value.

=AVERAGE(OFFSET($B2,MATCH(TODAY(),$B$2:$B$32,1)-7,1,7,1))+SUM($C$2:$C$32)

--
Thank you for your help and support