in excel, how do i get an average without including the zeros?

S

Season

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.
 
R

Ron Coderre

Try this:

=AVERAGE(IF(BZ28:CM28<>0, BZ28:CM28))
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

OR

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/COUNTIF(BZ28:CM28,"<>"&0)

Does that help?

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

XL2002, WinXP-Pro
 
R

Ron Coderre

Ooops! the second formula interprets blank cells as valid.

Either of these would correct that:

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/(COUNTIF(BZ28:CM28,">"&0)+COUNTIF(BZ28:CM28,"<"&0))

or

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/SUMPRODUCT(--(BZ28:CM28<>0))

Note: there are other variations if some of the cells may contain text.

Does that help?

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

XL2002, WinXP-Pro


Ron Coderre said:
Try this:

=AVERAGE(IF(BZ28:CM28<>0, BZ28:CM28))
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

OR

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/COUNTIF(BZ28:CM28,"<>"&0)

Does that help?

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

XL2002, WinXP-Pro


Season said:
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.
Click to expand...
Click to expand...
 
R

Ron Coderre

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
 
A

Anne Troy

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
 
R

Ron Coderre

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
 
A

Anne Troy

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
 
H

Harlan Grove

Ron Coderre wrote...
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))
Click to expand...
....

Major conceptual problem. If positive and negative values are both
valid, then wouldn't zero also be valid? It may make sense to average
positive and negative numbers separately, but it never makes sense to
average positive and negative numbers together while excluding zeros
from the average. The formulas with >0 conditions were correct. Those
with <>0 conditions aren't.
 
S

Season

that seems to do the trick. thank you very much, i really appreciate it.

Ron Coderre said:
Ooops! the second formula interprets blank cells as valid.

Either of these would correct that:

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/(COUNTIF(BZ28:CM28,">"&0)+COUNTIF(BZ28:CM28,"<"&0))

or

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/SUMPRODUCT(--(BZ28:CM28<>0))

Note: there are other variations if some of the cells may contain text.

Does that help?

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

XL2002, WinXP-Pro


Ron Coderre said:
Try this:

=AVERAGE(IF(BZ28:CM28<>0, BZ28:CM28))
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

OR

=SUMPRODUCT((BZ28:CM28<>0)*(BZ28:CM28))/COUNTIF(BZ28:CM28,"<>"&0)

Does that help?

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

XL2002, WinXP-Pro


Season said:
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.
Click to expand...
Click to expand...
Click to expand...
 
S

Season

this formula works an so does Ron's. thank you guys for your help. And i
would not any negative numbers in my calculations, i just set the original
equation up off of the office help and i am not that great with figuring
these things out so i guess i set it up wrong (to answer a question you asked
below). Hopefully i am getting better, with help from this site of couse. :)
thanks again.
 
Top