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?