software

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:CM28lt;gt;0, BZ28:CM28, quot; quot;))

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,quot;gt;0quot;)/COUNTIF(B1:B6,quot;gt;0quot;)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

quot;Seasonquot; gt; wrote in message
...
gt;I am using a formula to include all values greater than zero in my average
gt; which has worked fine until i tried to extend the parameters, then it
gt; gives
gt; me the Value? sign. Does anyone know another way to accomplish what i
gt; want?
gt;
gt; here is what i am using now.
gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt;
gt; i was trying to make the CM extend to CS, but for some reason it is not
gt; working. Anyway, i figure a different equation might be better than what
gt; i
gt; have and solve my problem.
gt;
gt;
Try this:

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

OR

=SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/COUNTIF(BZ28:CM28,quot;lt;gt;quot;amp;0)

Does that help?

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

XL2002, WinXP-Proquot;Seasonquot; wrote:

gt; I am using a formula to include all values greater than zero in my average
gt; which has worked fine until i tried to extend the parameters, then it gives
gt; me the Value? sign. Does anyone know another way to accomplish what i want?
gt;
gt; here is what i am using now.
gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt;
gt; i was trying to make the CM extend to CS, but for some reason it is not
gt; working. Anyway, i figure a different equation might be better than what i
gt; have and solve my problem.
gt;
gt;

Ooops! the second formula interprets blank cells as valid.

Either of these would correct that:

=SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/(COUNTIF(BZ28:CM28,quot;gt;quot;amp;0) COUNTIF(BZ28:CM28,quot;lt;quot;amp;0) )

or

=SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/SUMPRODUCT(--(BZ28:CM28lt;gt;0))

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

Does that help?

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

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; =AVERAGE(IF(BZ28:CM28lt;gt;0, BZ28:CM28))
gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; press [Enter].
gt;
gt; OR
gt;
gt; =SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/COUNTIF(BZ28:CM28,quot;lt;gt;quot;amp;0)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Seasonquot; wrote:
gt;
gt; gt; I am using a formula to include all values greater than zero in my average
gt; gt; which has worked fine until i tried to extend the parameters, then it gives
gt; gt; me the Value? sign. Does anyone know another way to accomplish what i want?
gt; gt;
gt; gt; here is what i am using now.
gt; gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt; gt;
gt; gt; i was trying to make the CM extend to CS, but for some reason it is not
gt; gt; working. Anyway, i figure a different equation might be better than what i
gt; gt; have and solve my problem.
gt; gt;
gt; gt;

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-Proquot;Anne Troyquot; wrote:

gt; =SUMIF(B1:B6,quot;gt;0quot;)/COUNTIF(B1:B6,quot;gt;0quot;)
gt; ************
gt; Hope it helps!
gt; Anne Troy
gt; www.OfficeArticles.com
gt;
gt; quot;Seasonquot; gt; wrote in message
gt; ...
gt; gt;I am using a formula to include all values greater than zero in my average
gt; gt; which has worked fine until i tried to extend the parameters, then it
gt; gt; gives
gt; gt; me the Value? sign. Does anyone know another way to accomplish what i
gt; gt; want?
gt; gt;
gt; gt; here is what i am using now.
gt; gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt; gt;
gt; gt; i was trying to make the CM extend to CS, but for some reason it is not
gt; gt; working. Anyway, i figure a different equation might be better than what
gt; gt; i
gt; gt; have and solve my problem.
gt; gt;
gt; gt;
gt;
gt;
gt;

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

quot;Ron Coderrequot; gt; wrote in message
...
gt; Hey, Anne...Nice and concise.
gt; I've used SUMPRODUCT so much at work today that I couldn't think of
gt; anything
gt; else!
gt;
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Anne Troyquot; wrote:
gt;
gt;gt; =SUMIF(B1:B6,quot;gt;0quot;)/COUNTIF(B1:B6,quot;gt;0quot;)
gt;gt; ************
gt;gt; Hope it helps!
gt;gt; Anne Troy
gt;gt; www.OfficeArticles.com
gt;gt;
gt;gt; quot;Seasonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am using a formula to include all values greater than zero in my
gt;gt; gt;average
gt;gt; gt; which has worked fine until i tried to extend the parameters, then it
gt;gt; gt; gives
gt;gt; gt; me the Value? sign. Does anyone know another way to accomplish what i
gt;gt; gt; want?
gt;gt; gt;
gt;gt; gt; here is what i am using now.
gt;gt; gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt;gt; gt;
gt;gt; gt; i was trying to make the CM extend to CS, but for some reason it is not
gt;gt; gt; working. Anyway, i figure a different equation might be better than
gt;gt; gt; what
gt;gt; gt; i
gt;gt; gt; have and solve my problem.
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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,quot;lt;gt;0quot;)/SUMPRODUCT((A1:A10lt;gt;0)*ISNUMBER(A1:A10))

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

XL2002, WinXP-Proquot;Anne Troyquot; wrote:

gt; And I am crap with sumproduct, Ron. I wondered if there might be something
gt; wrong with my formula, like it might fail for certain reasons, and was going
gt; to ask, but decided to wait and see if anybody yelled at me.
gt; Glad to hear it'll work. Thanks!
gt; ************
gt; Hope it helps!
gt; Anne Troy
gt; www.OfficeArticles.com
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; ...
gt; gt; Hey, Anne...Nice and concise.
gt; gt; I've used SUMPRODUCT so much at work today that I couldn't think of
gt; gt; anything
gt; gt; else!
gt; gt;
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Anne Troyquot; wrote:
gt; gt;
gt; gt;gt; =SUMIF(B1:B6,quot;gt;0quot;)/COUNTIF(B1:B6,quot;gt;0quot;)
gt; gt;gt; ************
gt; gt;gt; Hope it helps!
gt; gt;gt; Anne Troy
gt; gt;gt; www.OfficeArticles.com
gt; gt;gt;
gt; gt;gt; quot;Seasonquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I am using a formula to include all values greater than zero in my
gt; gt;gt; gt;average
gt; gt;gt; gt; which has worked fine until i tried to extend the parameters, then it
gt; gt;gt; gt; gives
gt; gt;gt; gt; me the Value? sign. Does anyone know another way to accomplish what i
gt; gt;gt; gt; want?
gt; gt;gt; gt;
gt; gt;gt; gt; here is what i am using now.
gt; gt;gt; gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt; gt;gt; gt;
gt; gt;gt; gt; i was trying to make the CM extend to CS, but for some reason it is not
gt; gt;gt; gt; working. Anyway, i figure a different equation might be better than
gt; gt;gt; gt; what
gt; gt;gt; gt; i
gt; gt;gt; gt; have and solve my problem.
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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 quot;no reportquot;, 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

quot;Ron Coderrequot; gt; wrote in message
...
gt; Hmmm..when I enter negative numbers in some of the cells, the average is
gt; wrong.
gt;
gt; I fixed it with (what else!: SUMPRODUCT):
gt; =SUMIF(A1:A10,quot;lt;gt;0quot;)/SUMPRODUCT((A1:A10lt;gt;0)*ISNUMBER(A1:A10))
gt;
gt; (but, I'm sure there are other ways, too)
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Anne Troyquot; wrote:
gt;
gt;gt; And I am crap with sumproduct, Ron. I wondered if there might be
gt;gt; something
gt;gt; wrong with my formula, like it might fail for certain reasons, and was
gt;gt; going
gt;gt; to ask, but decided to wait and see if anybody yelled at me.
gt;gt; Glad to hear it'll work. Thanks!
gt;gt; ************
gt;gt; Hope it helps!
gt;gt; Anne Troy
gt;gt; www.OfficeArticles.com
gt;gt;
gt;gt; quot;Ron Coderrequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hey, Anne...Nice and concise.
gt;gt; gt; I've used SUMPRODUCT so much at work today that I couldn't think of
gt;gt; gt; anything
gt;gt; gt; else!
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; ***********
gt;gt; gt; Regards,
gt;gt; gt; Ron
gt;gt; gt;
gt;gt; gt; XL2002, WinXP-Pro
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Anne Troyquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; =SUMIF(B1:B6,quot;gt;0quot;)/COUNTIF(B1:B6,quot;gt;0quot;)
gt;gt; gt;gt; ************
gt;gt; gt;gt; Hope it helps!
gt;gt; gt;gt; Anne Troy
gt;gt; gt;gt; www.OfficeArticles.com
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Seasonquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I am using a formula to include all values greater than zero in my
gt;gt; gt;gt; gt;average
gt;gt; gt;gt; gt; which has worked fine until i tried to extend the parameters, then
gt;gt; gt;gt; gt; it
gt;gt; gt;gt; gt; gives
gt;gt; gt;gt; gt; me the Value? sign. Does anyone know another way to accomplish what
gt;gt; gt;gt; gt; i
gt;gt; gt;gt; gt; want?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; here is what i am using now.
gt;gt; gt;gt; gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; i was trying to make the CM extend to CS, but for some reason it is
gt;gt; gt;gt; gt; not
gt;gt; gt;gt; gt; working. Anyway, i figure a different equation might be better than
gt;gt; gt;gt; gt; what
gt;gt; gt;gt; gt; i
gt;gt; gt;gt; gt; have and solve my problem.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Ron Coderre wrote...
gt;Hmmm..when I enter negative numbers in some of the cells, the average is wrong.
gt;
gt;I fixed it with (what else!: SUMPRODUCT):
gt;=SUMIF(A1:A10,quot;lt;gt;0quot;)/SUMPRODUCT((A1:A10lt;gt;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 gt;0 conditions were correct. Those
with lt;gt;0 conditions aren't.that seems to do the trick. thank you very much, i really appreciate it.

quot;Ron Coderrequot; wrote:

gt; Ooops! the second formula interprets blank cells as valid.
gt;
gt; Either of these would correct that:
gt;
gt; =SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/(COUNTIF(BZ28:CM28,quot;gt;quot;amp;0) COUNTIF(BZ28:CM28,quot;lt;quot;amp;0) )
gt;
gt; or
gt;
gt; =SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/SUMPRODUCT(--(BZ28:CM28lt;gt;0))
gt;
gt; Note: there are other variations if some of the cells may contain text.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; =AVERAGE(IF(BZ28:CM28lt;gt;0, BZ28:CM28))
gt; gt; Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
gt; gt; press [Enter].
gt; gt;
gt; gt; OR
gt; gt;
gt; gt; =SUMPRODUCT((BZ28:CM28lt;gt;0)*(BZ28:CM28))/COUNTIF(BZ28:CM28,quot;lt;gt;quot;amp;0)
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Seasonquot; wrote:
gt; gt;
gt; gt; gt; I am using a formula to include all values greater than zero in my average
gt; gt; gt; which has worked fine until i tried to extend the parameters, then it gives
gt; gt; gt; me the Value? sign. Does anyone know another way to accomplish what i want?
gt; gt; gt;
gt; gt; gt; here is what i am using now.
gt; gt; gt; =average(if(BZ28:CM28lt;gt;0, BZ28:CM28, quot; quot;))
gt; gt; gt;
gt; gt; gt; i was trying to make the CM extend to CS, but for some reason it is not
gt; gt; gt; working. Anyway, i figure a different equation might be better than what i
gt; gt; gt; have and solve my problem.
gt; gt; gt;
gt; gt; gt;