# Zeros Averages and daverage

• Hi there.

My boss has given me a great problem to sort out.

He has a formula
=IF(COUNTIF('Call Sheets'!\$Y\$7:\$Y\$692,L20)=0,0,DAVERAGE('Call Sheets'!A\$7:AM\$109,"curtatne",Formula!A1:B2))

The problem is that it is also including any 0's in the average. eg, average of 7+0=3.5 and he wants it to exlcude the 0's.

I can see what the formula is doing, and I read about using arrays, I just can't quite see how to apply them to the formula above. Can anyone suggest anything?

I'm thinking I may need to create some hidden columns with seperate chunks of formula in to get what I need.

Any help would be greatly appreciated.

Many thanks
Lisa

• Re: Zeros Averages and daverage

I'm not familiar with DAVERAGE, and without an attachment I couldn't investigate it, but in general, averages excluding zeroes can be done with SUM or SUMIF, divided by COUNT or COUNTIF.

• Re: Zeros Averages and daverage

Like this?

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

Or, use DAVERAGE with a criteria under the copied heading of <>0

• Re: Zeros Averages and daverage

I have attached a copy of the document I'm trying to work on. (I didn't design it, I just have to use it). I think my boss is using the Daverage function, because of the possiblility of the column positions changing.

The column giving me problems is F, on the Overview Sheet.

The Overview sheet contains the report
The Call Sheets contains the raw data
The formula sheet contains some data he is using in his formulas.

Again, any help is much appreciated.

Thanks
Lisa

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!