Rolling Monthly Average

[Colin Hayes]

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

 

[Pete_UK]

What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are
blank then you could just use:

=AVERAGE(D416,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

 

[Colin Hayes]

What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are
blank then you could just use:

=AVERAGE(D416,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

 

[Damon Longworth]

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/

What do you have in cells D5, D7, D9 and I5, I7, I9 etc? If these are
blank then you could just use:

=AVERAGE(D416,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

 

[Peo Sjoblom]

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(D416,I4:I16)/(COUNTIF(D416,">0")+(COUNTIF(I4:I16,">0")))


--


Regards,


Peo Sjoblom

 

[Colin Hayes]

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(D416,I4:I16)/(COUNTIF(D416,">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