Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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. -- Thank you for your help and support |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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 --------------- Jacob Skaria "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. -- Thank you for your help and support |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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? -- Biff Microsoft Excel MVP "bbal20" wrote in message ... 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. -- Thank you for your help and support |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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. -- Thank you for your help and support "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. -- Thank you for your help and support |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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? -- Thank you for your help and support "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. -- Thank you for your help and support |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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 --------------- Jacob Skaria "bbal20" wrote: 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? -- Thank you for your help and support "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. -- Thank you for your help and support |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "bbal20" wrote: 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? -- Thank you for your help and support "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. -- Thank you for your help and support |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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? -- Thank you for your help and support "Sean Timmons" wrote: 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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "bbal20" wrote: 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? -- Thank you for your help and support "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. -- Thank you for your help and support |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last 7 days
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? -- Thank you for your help and support "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. -- Thank you for your help and support |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Employee days worked (-Holidays, -weekends, Snow Days, etc) | Excel Discussion (Misc queries) | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions | |||
Convert decimal days to Days,hours, minutes | Excel Worksheet Functions |