excluding valuse from an average

[Guest]

I am setting up a tracking spread sheet and I would like to be able to
average the weekly values and then average this for a monthly and a
cumulative monthly average. The problem that I am having is there are
certain weeks that there won't be any values. Is there a way to exclude
these reading ( I am calling them "0" in this spread sheet ) and still be
able to use the average function?
Thanks in advance for any and all help in this matter

 

[Guest]

If you leave them blank rathr than zero, AVERAGE() will ignore them.

 

[Guest]

Enter this an array formula Ctrl+Shift+Enter

=AVERAGE(IF(A1:A100<>0,A1:A100))

or enter as normal:

=SUM(A1:A100)/(COUNTIF(A1:A100,">0")+COUNTIF(A1:A100,"<0"))

HTH