B
bbal20
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)
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)