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