| 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
"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. |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Hi Roger -
Sorry! Let me try to broaden the picture for you to help you if I can. Column B lists all 12 months January -December in rows 5 - 16. Column C is 2002 sales vaules for each month (row 5-16 with yearly average in row 17. Column D is 2003 sales vaules for each month (row 5-16 with yearly average in row 17). Column E is 2004 sales vaules for each month (row 5-16 with yearly average in row 17). Column F is 2005 sales vaules for each month (row 5-16 with yearly average in row 17. Column G is 2006 sales values in January - October (Nov & Dec will be added in rows 15&16 when it becomes available. YTD average is in row 17. Currently G19 is the last 12 months sales average. I need to keep a running average of the sales values for the most recent 12 months thus the need for the forumla that transends 2 columns that will adjust as I add sales values for the new month, each month. Yes you are correct that the most recent 12 months starts in Column F (2005), goes on to G (2006) and will go H as I move on to 2007. By that time however, there will be no need to reference F any longer as it (2005) will be out of the range of the most recent 12 months. Per your request, the full range of cells that can hold data in F & G is F5:G16. Don't know if this helps. Sorry I have been so narrow in my discription. I do appreciate your help, time & effort! Thanks again, Dewayne -- Dewayne "Roger Govier" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Hi Dewayne
Then my guess was almost correct. The formula I gave you will work for columns F, G and H i.e for the whole of 2005, 2006 and 2007 provided the cells in the months that have not yet been completed are empty. The formula is counting how many cells have values in each column, and deducting that value from 12. If then takes that number of rows before row 16, plus any rows that have been completed in the next column. You should be able to adjust it for future years by changing the appropriate column references. -- Regards Roger Govier "Dewayne" wrote in message ... Hi Roger - Sorry! Let me try to broaden the picture for you to help you if I can. Column B lists all 12 months January -December in rows 5 - 16. Column C is 2002 sales vaules for each month (row 5-16 with yearly average in row 17. Column D is 2003 sales vaules for each month (row 5-16 with yearly average in row 17). Column E is 2004 sales vaules for each month (row 5-16 with yearly average in row 17). Column F is 2005 sales vaules for each month (row 5-16 with yearly average in row 17. Column G is 2006 sales values in January - October (Nov & Dec will be added in rows 15&16 when it becomes available. YTD average is in row 17. Currently G19 is the last 12 months sales average. I need to keep a running average of the sales values for the most recent 12 months thus the need for the forumla that transends 2 columns that will adjust as I add sales values for the new month, each month. Yes you are correct that the most recent 12 months starts in Column F (2005), goes on to G (2006) and will go H as I move on to 2007. By that time however, there will be no need to reference F any longer as it (2005) will be out of the range of the most recent 12 months. Per your request, the full range of cells that can hold data in F & G is F5:G16. Don't know if this helps. Sorry I have been so narrow in my discription. I do appreciate your help, time & effort! Thanks again, Dewayne -- Dewayne "Roger Govier" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Roger -
Thank you for all the help and your time. Dewayne -- Dewayne "Roger Govier" wrote: Hi Dewayne Then my guess was almost correct. The formula I gave you will work for columns F, G and H i.e for the whole of 2005, 2006 and 2007 provided the cells in the months that have not yet been completed are empty. The formula is counting how many cells have values in each column, and deducting that value from 12. If then takes that number of rows before row 16, plus any rows that have been completed in the next column. You should be able to adjust it for future years by changing the appropriate column references. -- Regards Roger Govier "Dewayne" wrote in message ... Hi Roger - Sorry! Let me try to broaden the picture for you to help you if I can. Column B lists all 12 months January -December in rows 5 - 16. Column C is 2002 sales vaules for each month (row 5-16 with yearly average in row 17. Column D is 2003 sales vaules for each month (row 5-16 with yearly average in row 17). Column E is 2004 sales vaules for each month (row 5-16 with yearly average in row 17). Column F is 2005 sales vaules for each month (row 5-16 with yearly average in row 17. Column G is 2006 sales values in January - October (Nov & Dec will be added in rows 15&16 when it becomes available. YTD average is in row 17. Currently G19 is the last 12 months sales average. I need to keep a running average of the sales values for the most recent 12 months thus the need for the forumla that transends 2 columns that will adjust as I add sales values for the new month, each month. Yes you are correct that the most recent 12 months starts in Column F (2005), goes on to G (2006) and will go H as I move on to 2007. By that time however, there will be no need to reference F any longer as it (2005) will be out of the range of the most recent 12 months. Per your request, the full range of cells that can hold data in F & G is F5:G16. Don't know if this helps. Sorry I have been so narrow in my discription. I do appreciate your help, time & effort! Thanks again, Dewayne -- Dewayne "Roger Govier" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
12 Month Average
Hi
You're very welcome. Thanks for the feedback. Glad to know it worked for you. -- Regards Roger Govier "Dewayne" wrote in message ... Roger - Thank you for all the help and your time. Dewayne -- Dewayne "Roger Govier" wrote: Hi Dewayne Then my guess was almost correct. The formula I gave you will work for columns F, G and H i.e for the whole of 2005, 2006 and 2007 provided the cells in the months that have not yet been completed are empty. The formula is counting how many cells have values in each column, and deducting that value from 12. If then takes that number of rows before row 16, plus any rows that have been completed in the next column. You should be able to adjust it for future years by changing the appropriate column references. -- Regards Roger Govier "Dewayne" wrote in message ... Hi Roger - Sorry! Let me try to broaden the picture for you to help you if I can. Column B lists all 12 months January -December in rows 5 - 16. Column C is 2002 sales vaules for each month (row 5-16 with yearly average in row 17. Column D is 2003 sales vaules for each month (row 5-16 with yearly average in row 17). Column E is 2004 sales vaules for each month (row 5-16 with yearly average in row 17). Column F is 2005 sales vaules for each month (row 5-16 with yearly average in row 17. Column G is 2006 sales values in January - October (Nov & Dec will be added in rows 15&16 when it becomes available. YTD average is in row 17. Currently G19 is the last 12 months sales average. I need to keep a running average of the sales values for the most recent 12 months thus the need for the forumla that transends 2 columns that will adjust as I add sales values for the new month, each month. Yes you are correct that the most recent 12 months starts in Column F (2005), goes on to G (2006) and will go H as I move on to 2007. By that time however, there will be no need to reference F any longer as it (2005) will be out of the range of the most recent 12 months. Per your request, the full range of cells that can hold data in F & G is F5:G16. Don't know if this helps. Sorry I have been so narrow in my discription. I do appreciate your help, time & effort! Thanks again, Dewayne -- Dewayne "Roger Govier" wrote: 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 |
| 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 | |||
Linear Mode
