excluding valuse from an average
 

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

excluding valuse from an average
 

If you leave them blank rathr than zero, AVERAGE() will ignore them.
--
Gary's Student


"toot033" wrote:

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

excluding valuse from an average
 

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

"toot033" wrote:

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