| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
7 previous days average
Hi All,
Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
Change column to suit
=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
Hi there,
So, I pasted your function into my column and it worked well, however, how can i adjust this function so that it is dynamic. That is, regardless of where the formula is placed in the row for Oct 1, it calculates 7 days average prior to Oct 1. If i place it in row with date Sep 5, it will calculate 7 days average prior to Sep 5. Thanks in advance for your help. It is really appreciated. -- Carlee "Don Guillett" wrote: Change column to suit =AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
hi there,
Still no luck. Here is the code that exists in the column now. =SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78)) Problem is that this function does not average, AND it does look at only the previous 7 days of data in column AJ. Can you assist me? -- Carlee "Don Guillett" wrote: Place anywhere on the sheet except in column aj to average the last 7 rows of column aj. =AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi there, So, I pasted your function into my column and it worked well, however, how can i adjust this function so that it is dynamic. That is, regardless of where the formula is placed in the row for Oct 1, it calculates 7 days average prior to Oct 1. If i place it in row with date Sep 5, it will calculate 7 days average prior to Sep 5. Thanks in advance for your help. It is really appreciated. -- Carlee "Don Guillett" wrote: Change column to suit =AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
How about this
=AVERAGE(INDEX($AK$1:$AK$1000,ROW()):INDEX($AK$1:$ AK$1000,MAX(1,ROW()-6))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Carlee" wrote in message ... hi there, Still no luck. Here is the code that exists in the column now. =SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78)) Problem is that this function does not average, AND it does look at only the previous 7 days of data in column AJ. Can you assist me? -- Carlee "Don Guillett" wrote: Place anywhere on the sheet except in column aj to average the last 7 rows of column aj. =AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi there, So, I pasted your function into my column and it worked well, however, how can i adjust this function so that it is dynamic. That is, regardless of where the formula is placed in the row for Oct 1, it calculates 7 days average prior to Oct 1. If i place it in row with date Sep 5, it will calculate 7 days average prior to Sep 5. Thanks in advance for your help. It is really appreciated. -- Carlee "Don Guillett" wrote: Change column to suit =AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
HI Bob,
Can you do the same thing to this function: =SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78)) Essentially, I need to have this calculation listed in Column AK. Whatever the row this calculation is placed in column AK, I need the function to run based on the seven prior days for columns r and aj. Make sense? -- Carlee "Bob Phillips" wrote: How about this =AVERAGE(INDEX($AK$1:$AK$1000,ROW()):INDEX($AK$1:$ AK$1000,MAX(1,ROW()-6))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Carlee" wrote in message ... hi there, Still no luck. Here is the code that exists in the column now. =SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78)) Problem is that this function does not average, AND it does look at only the previous 7 days of data in column AJ. Can you assist me? -- Carlee "Don Guillett" wrote: Place anywhere on the sheet except in column aj to average the last 7 rows of column aj. =AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi there, So, I pasted your function into my column and it worked well, however, how can i adjust this function so that it is dynamic. That is, regardless of where the formula is placed in the row for Oct 1, it calculates 7 days average prior to Oct 1. If i place it in row with date Sep 5, it will calculate 7 days average prior to Sep 5. Thanks in advance for your help. It is really appreciated. -- Carlee "Don Guillett" wrote: Change column to suit =AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
Hi Carlee,
Unless I'm missing something here (quite probable!) All you need to do is put this in AK9 =IF(AJ9="","",AVERAGE(AJ3:AJ9)) and drag it down as far as your future needs require. The references update as you drag to give you a rolling 7 cell average. HTH Martin "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
it worked !! It worked!! It worked!!!
Thank you so very much for all of your help. -- Carlee "MartinW" wrote: Hi Carlee, Unless I'm missing something here (quite probable!) All you need to do is put this in AK9 =IF(AJ9="","",AVERAGE(AJ3:AJ9)) and drag it down as far as your future needs require. The references update as you drag to give you a rolling 7 cell average. HTH Martin "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
7 previous days average
Glad to hear that Carlee!
Regards Martin "Carlee" wrote in message ... it worked !! It worked!! It worked!!! Thank you so very much for all of your help. -- Carlee "MartinW" wrote: Hi Carlee, Unless I'm missing something here (quite probable!) All you need to do is put this in AK9 =IF(AJ9="","",AVERAGE(AJ3:AJ9)) and drag it down as far as your future needs require. The references update as you drag to give you a rolling 7 cell average. HTH Martin "Carlee" wrote in message ... Hi All, Question: What function can i use to create an average value of the previous 7 days? Column AJ contains data, which is added to everyday Column B contains the date In Column AK, I want to have a value, which calculates the average value, of the 7 previous values entered in column AJ. Data starts in row 3 for all columns. -- Carlee |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average of previous values | Excel Discussion (Misc queries) | |||
| Average based on the previous row | Excel Worksheet Functions | |||
| Subtract # of days from date, but if not sat, goto previous sat? | Excel Discussion (Misc queries) | |||
| 25 days previous to today? | Excel Discussion (Misc queries) | |||
| date functions for previous 60/90/180 days | Excel Worksheet Functions | |||
Linear Mode
