Last 7 days

[bbal20]

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.

 

[Jacob Skaria]

The below formula will give you the average of last 7 days. On 26th it will
return the average from 19th to 25th both inclusive..Please note that this is
an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula>}"

=AVERAGE(IF(A1:A100>=(TODAY()-7),B1:B100))

If this post helps click Yes

 

[T. Valko]

average of the last 7 days...based on the Now()

date and how many days are left in the month.

I don't understand that. Can you explain what that means? Getting the
average of the last 7 entries is no problem but what does the NOW() date and
how many days are left in the month have to do with it?

 

[bbal20]

Most certainly. I may have misspoke when saying I needed the Now(). The
formuls would probably need to use the TODAY() function. That is needed to
get the average of the last 7 days I would imagine. Something like TODAY()-7.
I'm basically trying to calculate the average amount of impressions over the
last 7 days to calculate the pacing rate if I were to maintain that average
each day for the remainder of the month. I hope that makes sence. Let me know.

 

[bbal20]

Hey Jacob. Thank you for the quick response. I think the formula may be
missing something. I may be wrong. How does this formula calculate the
pacing for the remaining days of the month? First the formula needs to figure
out the average for the last 7 days from TODAY() which it looks like you may
have done already, but we then have to take that average and apply it to the
remaining amount of days in the current month to figure out what I will
potentially end the month at. Any ideas?

 

[Jacob Skaria]

Do you have a target for each month? OR if you want to keep pace with the
last 7 days average; then the average itself will be the daily target...

Am i missing something?

If this post helps click Yes

 

[Sean Timmons]

I'm going to make an assumption here. Are you looking to determine the
average and sum from today to the end of the month?

If so, go to Tools > Add Ins... > Analysis Toolpak and use EOMONTH()


=(SUMIF(A:A,">=TODAY()-7",B:B)/COUNTIF(A:A,">=TODAY()-7")*(day(eomonth(today()))-day(today()))

should give average of last 7 days times number of days left in the month.

 

[Chip Pearson]

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)

 

[bbal20]

Almost. I'm trying to determine the average for the last seven days then take
that daily average and apply it to the remaining number of days in the month.
Lastly, the formula needs to add the determined daily average on top of what
has already been delivered. For example, today is the 26th so I need the
average from the 19th to the 25th. Once that is determined I need to assume
that average from now (26th) until EOM and it needs to be added on top of
what has already been actually delivered from the 1st to the 25th. That will
give me an overall pacing value for the month. Does that make sense?

 

[bbal20]

I don't have a goal per say. I'm trying to determine the average for the last
seven days then take
that daily average and apply it to the remaining number of days in the month.
Lastly, the formula needs to add the determined daily average on top of what
has already been delivered. For example, today is the 26th so I need the
average from the 19th to the 25th. Once that is determined I need to assume
that average from now (26th) until EOM and it needs to be added on top of
what has already been actually delivered from the 1st to the 25th. That will
give me an overall pacing value for the month. Does that make sense?