| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
12 Month Average
I calculate an average in cell G19 that includes the lastest 12 months
values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Thanks for any suggestions. -- Dewayne |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Hi
Post your existing formula. What cells hold the data that you wish to average? -- Regards Roger Govier "Dewayne" wrote in message ... I calculate an average in cell G19 that includes the lastest 12 months values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Thanks for any suggestions. -- Dewayne |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Roger -
The formula as requested is =average(F14:F16,G5:G13) Thanks, Dewayne -- Dewayne "Roger Govier" wrote: Hi Post your existing formula. What cells hold the data that you wish to average? -- Regards Roger Govier "Dewayne" wrote in message ... I calculate an average in cell G19 that includes the lastest 12 months values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Thanks for any suggestions. -- Dewayne |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Hi Dewayne
So, are you saying that next month, you want (F15:F17,G5:G13), or (F15:F16,G5:G14) or (F14:F16,G6:G14) Can you be a little more explicit as to what each range of cells holds, and how you want the sample for your average to move? -- Regards Roger Govier "Dewayne" wrote in message ... Roger - The formula as requested is =average(F14:F16,G5:G13) Thanks, Dewayne -- Dewayne "Roger Govier" wrote: Hi Post your existing formula. What cells hold the data that you wish to average? -- Regards Roger Govier "Dewayne" wrote in message ... I calculate an average in cell G19 that includes the lastest 12 months values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Thanks for any suggestions. -- Dewayne |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Roger -
The next month I would like the range to average (F15:F16,G5:G14), the following month (F16,G5:G15) and so on. The range of cells holds numbers such as 12.33, 11.52 etc. which I input for each month as we progress through the year. Hope this helps. Thanks again for your help. -- Dewayne "Roger Govier" wrote: Hi Dewayne So, are you saying that next month, you want (F15:F17,G5:G13), or (F15:F16,G5:G14) or (F14:F16,G6:G14) Can you be a little more explicit as to what each range of cells holds, and how you want the sample for your average to move? -- Regards Roger Govier "Dewayne" wrote in message ... Roger - The formula as requested is =average(F14:F16,G5:G13) Thanks, Dewayne -- Dewayne "Roger Govier" wrote: Hi Post your existing formula. What cells hold the data that you wish to average? -- Regards Roger Govier "Dewayne" wrote in message ... I calculate an average in cell G19 that includes the lastest 12 months values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Thanks for any suggestions. -- Dewayne |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Hi Dewayne
I feel as though I am having to tease each little piece of information from you one step at a time. Could you provide the broader picture. Why are the monthly cells in 2 different columns? What is the full range of cells that can hold data in column F and column G? Does it start at F5:F16 for the first 12 months, then go on to G5:G16 for the next 12 months, and will it go on to H5:H16 for the following 12 months? If so, then the following should work =AVERAGE( OFFSET($F$16,0,0,-(12-COUNT($G$5:$G$16)),1), OFFSET($G$16,0,0,-(12-COUNT($H$5:$H$16)),1), $H$5:$H$16) -- Regards Roger Govier "Dewayne" wrote in message ... Roger - The next month I would like the range to average (F15:F16,G5:G14), the following month (F16,G5:G15) and so on. The range of cells holds numbers such as 12.33, 11.52 etc. which I input for each month as we progress through the year. Hope this helps. Thanks again for your help. -- Dewayne "Roger Govier" wrote: Hi Dewayne So, are you saying that next month, you want (F15:F17,G5:G13), or (F15:F16,G5:G14) or (F14:F16,G6:G14) Can you be a little more explicit as to what each range of cells holds, and how you want the sample for your average to move? -- Regards Roger Govier "Dewayne" wrote in message ... Roger - The formula as requested is =average(F14:F16,G5:G13) Thanks, Dewayne -- Dewayne "Roger Govier" wrote: Hi Post your existing formula. What cells hold the data that you wish to average? -- Regards Roger Govier "Dewayne" wrote in message ... I calculate an average in cell G19 that includes the lastest 12 months values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Thanks for any suggestions. -- Dewayne |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
"Dewayne" wrote:
I calculate an average in cell G19 that includes the lastest 12 months values. Each month I need to update the formula to exclude the first of the last 12 months and include the new month. Currently I am doing this manually each month. Is there a way to automatically update the formula to include only the last 12 months in the average when new data is entered into a new cell? Suppose your initial data is in column E (e.g. E8:E19). Then put the following formula into G19 and copy down through as many cells as you want: =if(E19="", "", average(E8:E19)) When you copy down to G20 (e.g), the formula will be changed to: =if(E20="", "", average(E9:E20)) When you enter data into E20, G20 will automatically show the average of the 12 cells ending with E20. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
| 4 and 5 week months | Excel Discussion (Misc queries) | |||
| Calculating Month Average Exch Rate from Exchange Rates Calendar!!! | Excel Worksheet Functions | |||
| How do I get the average price per bid for an individual month? | Excel Discussion (Misc queries) | |||
| HELP with this function | Excel Worksheet Functions | |||
Hybrid Mode
