EXACTLY! I took it, however, that if one didn't want to include zero values,
one would not want to include negatives either. But after thinking about it,
of course, zero could mean "no report", too, and shouldn't be included. But
if someone can have positive or negative results, it stands to reason that a
zero result could be reported as well. I'm so confused. Doh! :)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Ron Coderre" wrote in message
...
Hmmm..when I enter negative numbers in some of the cells, the average is
wrong.

I fixed it with (what else!: SUMPRODUCT):
=SUMIF(A1:A10,"<0")/SUMPRODUCT((A1:A10<0)*ISNUMBER(A1:A10))

(but, I'm sure there are other ways, too)
***********
Regards,
Ron

XL2002, WinXP-Pro


"Anne Troy" wrote:

And I am crap with sumproduct, Ron. I wondered if there might be
something
wrong with my formula, like it might fail for certain reasons, and was
going
to ask, but decided to wait and see if anybody yelled at me. :)
Glad to hear it'll work. Thanks!
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Ron Coderre" wrote in message
...
Hey, Anne...Nice and concise.
I've used SUMPRODUCT so much at work today that I couldn't think of
anything
else!


***********
Regards,
Ron

XL2002, WinXP-Pro


"Anne Troy" wrote:

=SUMIF(B1:B6,"0")/COUNTIF(B1:B6,"0")
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Season" wrote in message
...
I am using a formula to include all values greater than zero in my
average
which has worked fine until i tried to extend the parameters, then
it
gives
me the Value? sign. Does anyone know another way to accomplish what
i
want?

here is what i am using now.
=average(if(BZ28:CM28<0, BZ28:CM28, " "))

i was trying to make the CM extend to CS, but for some reason it is
not
working. Anyway, i figure a different equation might be better than
what
i
have and solve my problem.