Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling Monthly Average
HI I have a puzzle - I wonder if anyone can assist. In cells D4,D6,D8,D12,D14 and D16 in have formula-based totals for the first 6 months of the year. In the equivalent cells in column I I have the totals for the last 6 months. I want to have , in cell G17 , a running monthly average for the year so far. It needs to divide the overall total thus far with the number of months used up so far. Clearly , months that are still zero should not be counted until they have content as this would skew the average. Can anyone help with a formula for G17? Any advice gratefully received. Best Wishes |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling Monthly Average
What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are
blank then you could just use: =AVERAGE(D4:D16,I4:I16) as the function will ignore any blank cells. Do you have formulae in the cells D4, D6, D8 etc which return a value, or do you type these values in yourself? Hope this helps. Pete On Jul 29, 9:49*pm, Colin Hayes wrote: HI I have a puzzle - I wonder if anyone can assist. In cells *D4,D6,D8,D12,D14 and D16 in have formula-based totals for the first 6 months of the year. In the equivalent cells in column I I have the totals for the last 6 months. I want to have , in cell G17 , a running monthly average for the year so far. It needs to divide the overall total thus far with the number of months used up so far. Clearly , months that are still zero should not be counted until they have content as this would skew the average. Can anyone help with a formula for G17? Any advice gratefully received. Best Wishes |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling Monthly Average
In article
, Pete_UK writes What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are blank then you could just use: =AVERAGE(D4:D16,I4:I16) as the function will ignore any blank cells. Do you have formulae in the cells D4, D6, D8 etc which return a value, or do you type these values in yourself? Hope this helps. Pete Hi Pete Yes , that's certainly very much along the right lines. The cells between the ones I specified are indeed all blank , so don't cause an issue. The formula as above divides the totals from the cells by 12. This is fine , but doesn't give the rolling average I was looking for , unfortunately. It's a month-on-month division I need , to give average for the year to date. For example : At present only cells D10 , D12 and D14 have any content. All the other cells are at zero. The total for the three above is 1852. The rolling monthly average thus far should therefore be 617. The formula is returning 154 , as it is dividing by 12 and not ignoring cells with nil content. Each cell has a formula which returns a value from another sheet. Can the formula above be modified to ignore zero values and only divide the total by the amount of cells with content above zero? Thanks again Pete Best Wishes Colin On Jul 29, 9:49*pm, Colin Hayes wrote: HI I have a puzzle - I wonder if anyone can assist. In cells *D4,D6,D8,D12,D14 and D16 in have formula-based totals for the first 6 months of the year. In the equivalent cells in column I I have the totals for the last 6 months. I want to have , in cell G17 , a running monthly average for the year so far. It needs to divide the overall total thus far with the number of months used up so far. Clearly , months that are still zero should not be counted until they have content as this would skew the average. Can anyone help with a formula for G17? Any advice gratefully received. Best Wishes |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling Monthly Average
Try something similar to this:
=AVERAGE(OFFSET(D8,0,0,2*MONTH(NOW())-1,1)) It is simple if you use a calendar year, but other fiscal years can use the same logic. -- Damon Longworth Excel / Access User Conference Cambridge, England Nov 29 - Dec 1, 2007 Sydney, Australia March 12 - 14, 2008 www.ExcelUserConference.com/ "Colin Hayes" wrote in message ... In article , Pete_UK writes What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are blank then you could just use: =AVERAGE(D4:D16,I4:I16) as the function will ignore any blank cells. Do you have formulae in the cells D4, D6, D8 etc which return a value, or do you type these values in yourself? Hope this helps. Pete Hi Pete Yes , that's certainly very much along the right lines. The cells between the ones I specified are indeed all blank , so don't cause an issue. The formula as above divides the totals from the cells by 12. This is fine , but doesn't give the rolling average I was looking for , unfortunately. It's a month-on-month division I need , to give average for the year to date. For example : At present only cells D10 , D12 and D14 have any content. All the other cells are at zero. The total for the three above is 1852. The rolling monthly average thus far should therefore be 617. The formula is returning 154 , as it is dividing by 12 and not ignoring cells with nil content. Each cell has a formula which returns a value from another sheet. Can the formula above be modified to ignore zero values and only divide the total by the amount of cells with content above zero? Thanks again Pete Best Wishes Colin On Jul 29, 9:49 pm, Colin Hayes wrote: HI I have a puzzle - I wonder if anyone can assist. In cells D4,D6,D8,D12,D14 and D16 in have formula-based totals for the first 6 months of the year. In the equivalent cells in column I I have the totals for the last 6 months. I want to have , in cell G17 , a running monthly average for the year so far. It needs to divide the overall total thus far with the number of months used up so far. Clearly , months that are still zero should not be counted until they have content as this would skew the average. Can anyone help with a formula for G17? Any advice gratefully received. Best Wishes |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling Monthly Average
If you change the formula that returns the zeros if they are blank then the
average formula should work For instance =IF(A2="","",A2) would return a blank and Pete's formula would ignore that cell but if you use =A2 where A2 is blank then that cell returns zero and will be included Another way assuming that the monthly totals can never be zero =SUM(D4:D16,I4:I16)/(COUNTIF(D4:D16,"0")+(COUNTIF(I4:I16,"0"))) -- Regards, Peo Sjoblom "Colin Hayes" wrote in message ... In article , Pete_UK writes What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are blank then you could just use: =AVERAGE(D4:D16,I4:I16) as the function will ignore any blank cells. Do you have formulae in the cells D4, D6, D8 etc which return a value, or do you type these values in yourself? Hope this helps. Pete Hi Pete Yes , that's certainly very much along the right lines. The cells between the ones I specified are indeed all blank , so don't cause an issue. The formula as above divides the totals from the cells by 12. This is fine , but doesn't give the rolling average I was looking for , unfortunately. It's a month-on-month division I need , to give average for the year to date. For example : At present only cells D10 , D12 and D14 have any content. All the other cells are at zero. The total for the three above is 1852. The rolling monthly average thus far should therefore be 617. The formula is returning 154 , as it is dividing by 12 and not ignoring cells with nil content. Each cell has a formula which returns a value from another sheet. Can the formula above be modified to ignore zero values and only divide the total by the amount of cells with content above zero? Thanks again Pete Best Wishes Colin On Jul 29, 9:49 pm, Colin Hayes wrote: HI I have a puzzle - I wonder if anyone can assist. In cells D4,D6,D8,D12,D14 and D16 in have formula-based totals for the first 6 months of the year. In the equivalent cells in column I I have the totals for the last 6 months. I want to have , in cell G17 , a running monthly average for the year so far. It needs to divide the overall total thus far with the number of months used up so far. Clearly , months that are still zero should not be counted until they have content as this would skew the average. Can anyone help with a formula for G17? Any advice gratefully received. Best Wishes |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling Monthly Average
In article , Peo Sjoblom
writes If you change the formula that returns the zeros if they are blank then the average formula should work For instance =IF(A2="","",A2) would return a blank and Pete's formula would ignore that cell but if you use =A2 where A2 is blank then that cell returns zero and will be included Another way assuming that the monthly totals can never be zero =SUM(D4:D16,I4:I16)/(COUNTIF(D4:D16,"0")+(COUNTIF(I4:I16,"0"))) HI OK thanks for that. I have used the second formula you suggested and this work perfectly - thanks! Best Wishes Colin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Rolling Monthly Amounts to Annual Monthly Amounts | Excel Discussion (Misc queries) | |||
How do I set up a rolling monthly report in Excel? | Excel Discussion (Misc queries) | |||
Rolling Average | Excel Discussion (Misc queries) | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions |