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.

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

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" wrote:

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.

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" wrote:

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" wrote:

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.

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.

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.

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.

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.

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

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.

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

"Ron Coderre" wrote:

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" wrote:

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" wrote:

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.

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.

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