Average

[Guest]

I'm trying to average a group of numbers. I do not want
to include the blank or zeros. I used the following to
exclude the zeros but it is still including the blanks.

Thanks for any help you might be able to send me.

=AVERAGE(SUM(K5:K73)/COUNTIF(K5:K73,"<>0"))

 

[Jason Morin]

How about:

=SUM(K5:K73)/(COUNT(K5:K73)-COUNTIF(K5:K73,0))

HTH
Jason
Atlanta, GA

 

[Don Guillett]

use this idea. it must be ARRAY entered with CTRL+SHIFT+ENTER
=AVERAGE(IF(C1:C100<>0,C1:C100))

 

[Guest]

Jason, Thank you very much. It works great.

-----Original Message-----
How about:

=SUM(K5:K73)/(COUNT(K5:K73)-COUNTIF(K5:K73,0))

HTH
Jason
Atlanta, GA

.

 

[Harlan Grove]

I'm trying to average a group of numbers. I do not want
to include the blank or zeros. I used the following to
exclude the zeros but it is still including the blanks.

AVERAGE always excludes blank cells. COUNTIF using either "<0" or ">0" will
exclude blank cells and either nonnegative or nonpositive numbers.

If you want to average only positive numbers, you could use the array
formula

=AVERAGE(IF(Data>0,Data))

or the nonarray formula

=SUMIF(Data,">0")/COUNTIF(Data,">0")

If you have positive and negative numbers in your data, and you'd include
both positive and negative numbers in your average, then you should also
include zeros in your average.