Setting up a formula for an annualized amount

[Sue]

I am trying to set up a formula in a spreadsheet that will
figure an annualized amount of data but will divide by the
current number of cells that have values greater than 0.
I am struggling with how to set it up so that Excel will
count the number of cells and then divide by that number.
Here is my example:

In cells C11:N11 I have values for Jan - Dec 2004. Some
of those cells have 0's in them that will change as we get
data each month.

Jan Feb Mar Apr May June ....... Total Annualized
3 5 7 7 0 0 ........ 22 ?

I am wanting excel to total all of the cells and to divide
by the count of the cells that contain values greater than
0 then multiply by 12. So, in May, I would like it to add
Jan through April's data and to divide by 4 (number of
months containing data and then multiply by 12.

I originally set my formula up as:=O11/COUNT(C11:N11)*12
However, it is including in the count the cells that had
values of 0.

Is there an easier way to set this up than going in each
month and changing the count value? Thanks so much!

 

[Bernard Liengme]

Hi Sue,
Try this: =SUM(C11:N11)*12/COUNTIF(C11:N11,">0")
If all the values are 0 you get a DIV by Zero error. To avoid this
=IF(COUNTIF(C11:N11)>0,=SUM(C11:N11)*12/COUNTIF(C11:N11,">0"),"No data")

Best wishes