average for year
 

Hi wizards

I have a workbook with sheets for each month. The 13th sheet is for the
year totals. I would like to average the totals for the months on a rolling
basis. for example add jan,feb,march etc then divide by the number of
months. I have tried =sum(jan!e18:dec!e18)/counta(jan!e18:dec!e18) which
divides by 12 but if it is only august then I need it to divide by 9. Also
when I put the range in as an array it won't work as the cells I am taking my
info from contain a formula and unticking "show zeros" has no effect.

Hope you can help

average for year
 

No time to test but try
=SUM(jan:dec!E18)/COUNT(jan:dec!E18)

Would help to know what is in E18 - number or formula?

Please specify what you mean by array - do you mean just a range of cells?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Flower" wrote in message
...
Hi wizards

I have a workbook with sheets for each month. The 13th sheet is for the
year totals. I would like to average the totals for the months on a
rolling
basis. for example add jan,feb,march etc then divide by the number of
months. I have tried =sum(jan!e18:dec!e18)/counta(jan!e18:dec!e18) which
divides by 12 but if it is only august then I need it to divide by 9.
Also
when I put the range in as an array it won't work as the cells I am taking
my
info from contain a formula and unticking "show zeros" has no effect.

Hope you can help