Countif function
 

I am trying to do an average of values in a range of cells across colunms,
where only values other than zero are counted in the divisor. For example, I
have 4000, 0, 0 in my range and I want it to average the sum by 1, and not by
3 since the other two values are zero. If I then add a value in the string,
as 4000, 2000, 0, my formula should then automatically average the sum by 2.
My formula is as follows:

=IF(ISERROR(+SUM(Z54:AD54)/(COUNTIF(Z54:AD54,"<0"))),0,(+SUM(Z54:AD54)/
(COUNTIF(Z54:AD54,"<0"))))

I have done this type of formula before using only positive values as in "0"
and it has worked. However, this time I have both positive and negative
values and I want the formula to count all numbers other than zero in the
denominator. The problem is that even though I have specified not equal to
zero, i.e. "<0", is still counts zero as a number and divides by 3 as in the
case of 4000, 0, 0. I have tried every variation that I can think of to solve
this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is
zero) and nothing works. Can someone help me out on this?

Try...

=IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),SUM(Z54:AD54)/(COUNT(Z54:AD54)-CO
UNTIF(Z54:AD54,0)),0)

or

=IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),AVERAGE(IF(Z54:AD54,Z54:AD54)) ,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <551c6af0b1c5e@uwe,
"ponygirl via OfficeKB.com" <u14421@uwe wrote:

I am trying to do an average of values in a range of cells across colunms,
where only values other than zero are counted in the divisor. For example, I
have 4000, 0, 0 in my range and I want it to average the sum by 1, and not by
3 since the other two values are zero. If I then add a value in the string,
as 4000, 2000, 0, my formula should then automatically average the sum by 2.
My formula is as follows:

=IF(ISERROR(+SUM(Z54:AD54)/(COUNTIF(Z54:AD54,"<0"))),0,(+SUM(Z54:AD54)/
(COUNTIF(Z54:AD54,"<0"))))

I have done this type of formula before using only positive values as in "0"
and it has worked. However, this time I have both positive and negative
values and I want the formula to count all numbers other than zero in the
denominator. The problem is that even though I have specified not equal to
zero, i.e. "<0", is still counts zero as a number and divides by 3 as in the
case of 4000, 0, 0. I have tried every variation that I can think of to solve
this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is
zero) and nothing works. Can someone help me out on this?

Does this *array* formula work for you:

=AVERAGE(IF(Z54:AD54<0,Z54:AD54))
?
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"ponygirl via OfficeKB.com" <u14421@uwe wrote in message
news:551c6af0b1c5e@uwe...
I am trying to do an average of values in a range of cells across colunms,
where only values other than zero are counted in the divisor. For example,
I
have 4000, 0, 0 in my range and I want it to average the sum by 1, and not
by
3 since the other two values are zero. If I then add a value in the
string,
as 4000, 2000, 0, my formula should then automatically average the sum by
2.
My formula is as follows:

=IF(ISERROR(+SUM(Z54:AD54)/(COUNTIF(Z54:AD54,"<0"))),0,(+SUM(Z54:AD54)/
(COUNTIF(Z54:AD54,"<0"))))

I have done this type of formula before using only positive values as in
"0"
and it has worked. However, this time I have both positive and negative
values and I want the formula to count all numbers other than zero in the
denominator. The problem is that even though I have specified not equal to
zero, i.e. "<0", is still counts zero as a number and divides by 3 as in
the
case of 4000, 0, 0. I have tried every variation that I can think of to
solve
this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4
is
zero) and nothing works. Can someone help me out on this?

Thanks! This works, except that I need to incorporate the ISERROR function
into the formula so that I don't get the #DIV0! error. Can you offer any
suggestions for that?

PG


RagDyer wrote:
Does this *array* formula work for you:

=AVERAGE(IF(Z54:AD54<0,Z54:AD54))
?
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

I am trying to do an average of values in a range of cells across colunms,
where only values other than zero are counted in the divisor. For example, I
[quoted text clipped - 14 lines]
this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is
zero) and nothing works. Can someone help me out on this?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Works great! Thanks!

PG

Domenic wrote:
Try...

=IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),SUM(Z54:AD54)/(COUNT(Z54:AD54)-CO
UNTIF(Z54:AD54,0)),0)

or

=IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),AVERAGE(IF(Z54:AD54,Z54:AD54)) ,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

I am trying to do an average of values in a range of cells across colunms,
where only values other than zero are counted in the divisor. For example, I
[quoted text clipped - 14 lines]
this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is
zero) and nothing works. Can someone help me out on this?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

This will give you an empty ("") cell.
If you would prefer a zero, just replace the"" in the formula with a 0.

=IF(ISERR(AVERAGE(Z54:AD54)),"",AVERAGE(IF(Z54:AD5 4<0,Z54:AD54)))

Also array entered - CSE
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ponygirl via OfficeKB.com" <u14421@uwe wrote in message
news:5525e1b75936d@uwe...
Thanks! This works, except that I need to incorporate the ISERROR function
into the formula so that I don't get the #DIV0! error. Can you offer any
suggestions for that?

PG


RagDyer wrote:
Does this *array* formula work for you:

=AVERAGE(IF(Z54:AD54<0,Z54:AD54))
?
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

I am trying to do an average of values in a range of cells across
colunms,
where only values other than zero are counted in the divisor. For
example, I
[quoted text clipped - 14 lines]
this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell
B4 is
zero) and nothing works. Can someone help me out on this?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1