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

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

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

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

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

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