Exclude Zero in Average

[Pam]

I have a formula in cell C33 that averages the range
C2:C32. Some cells have a zero. How can I alter the
formula to exclude the zeros?

Thank you

 

[Don Guillett]

Use this array formula by using ctrl+shift+enter CSE when entering or
editing.

=AVERAGE(IF(A12:A15>0,A12:A15))

 

[Aladin Akyurek]

=AVERAGE(IF(C2:C32,C2:C32))

which must be array-entered (with control+shift+enter).

This formula will include any non-zero value in C2:C32 in the average.

 

[David J. Braden]

Pam,
There's yet another way than what Dan and Aladin suggested, namely
=SUM(rng)/COUNTIF(rng,"<>0"). It doesn't need to be specified as an array
formula, which might make it more robust to later worksheet editing.

If you want the fastest-executing statement, though (valuable in
simulations), then Aladin's suggestion is the way to go (btw, Dan's only
includes *positive* valies in the average; Aladin's get's at the non-zeroes
in an efficient way).

One caveat: You might have cells that *display* 0, but aren't; they are
instead mighty small values. To exclude values from the average whose
magnitude is <= some nonnegative epsilon, you can use
=AVERAGE(IF(ABS(rng)>epsilon,rng))
ctrl-shift-entered, meaning hold the ctrl and shift keys down at the same
time while pressing Enter.

More generally, to exclude values within epsilon of some particular x, you
can use
=AVERAGE(IF(ABS(rng-x)>epsilon,rng))

And still more generally <g>, as long as the function f() you are using in
Excel can handle array arguments, *and it skips over non-numeric data*,
then f(IF(ABS(rng-x)>epsilon,rng)) does the trick; you could use this with,
say, STDEV or an apropriately written custom function.
HTH
Dave Braden

 

[Harlan Grove]

If you want the fastest-executing statement, though (valuable in
simulations), then Aladin's suggestion is the way to go (btw, Dan's only
includes *positive* valies in the average; Aladin's get's at the non-zeroes
in an efficient way).

At the risk of opening another can of worms, Don's approach makes more
sense. There's no good reason to exclude zeros from an average while
including both positive and negative values. Blank cells and text would be
excluded anyway. Average of strictly positive and, separately, strictly
negative values could serve some purpose, but not average of all but zero
values.

 

[Harlan Grove]

What if, in a particular case, 0 is an impossible result, and the
user has (erroneously) used 0 to mean "not available" rather than
leaving the cell blank?

I can't think of a *continuous* random variable that might have positive or
negative values but not zero values. I suppose there might be some discrete
random variables that could, but using zero rather than #N/A or, much
better, text would be questionable. Indeed, if the OP used text, then the OP
could use the AVERAGE function as-is. So, is there any place in this or any
other newsgroup to suggest using text values, such as "n/a", rather than
zero to represent excluded cases?

What I suspect (and only the OP would know for sure) is that the OP is
trying to find a conditional mean of positive values.

 

[Jerry W. Lewis]

Excel's lack of a true missing value makes the issue of excluding zero a
common one, but you correctly note that it should only be done with
oversight if both positive and negative values can occur.

Jerry

 

[David J. Braden]

Harlan, Myrna, Jerry, and Don:

Good points all.
I have trouble reading my own mind, much less anyone else's. If someone, for
whatever reason, posts they want to exclude only zeros, then I'll point out
what is most efficient for that case. Personally, I was surprised that the
AVERAGE/IF combo is so much faster than the SUM/COUNTIF combo.

It's also conceivable that the data are non-negative, and the OP wants to
exclude zero values (and Harlan, forget the (even right-) continuous case on
this one: there must be some lumpiness in Pam's set, else why bother?). In
that case I'd still go with Aladin's suggestion. Don's ">" condition is
undeniably stricter than what was requested (though it may be the most
appropriate condition). Simply raising the issue, as you folks have done,
might perhaps get the OP thinking differently about the problem.

It's such a common question, I would think by now we'd have a nice post we
could refer folks to on this. I tried earlier in this thread; maybe y'all
could fix it up a bit?

BTW, I sure meant no slam at Don; I hope that's clear. I'm genuinely
grateful for his having helped the OP in what may well do the trick, and
having sparked this thread.

Regards,
Dave Braden

 

[Don Guillett]

BTW, I sure meant no slam at Don;
I'm very hard to offend and often make mistakes...<G>