I am looking to average a range of numbers, but I want to exclude any cell that has a zero from the Average. In this particular case, the zero's are filler only and have no real meaning but they are distorting my figure.

I can't seem to find the magic formula to make it function.

Brian

Brian,

Sum the range and divide by CountIf(cell < 0)

SteveM


Brian wrote:
I am looking to average a range of numbers, but I want to exclude any cell that has a zero from the Average. In this particular case, the zero's are filler only and have no real meaning but they are distorting my figure.

I can't seem to find the magic formula to make it function.

Brian

Brian~

You can use:
=Sum(Range)/CountIf(Range, "<0")

Jay

Brian wrote:
I am looking to average a range of numbers, but I want to exclude any cell that has a zero from the Average. In this particular case, the zero's are filler only and have no real meaning but they are distorting my figure.

I can't seem to find the magic formula to make it function.

Brian

=AVERAGE(IF(rng<0,rng))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

If, as I suspect you actually want numbers 0, just use

=AVERAGE(IF(rng0,rng))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Brian" wrote in message
news:2006623101427.284606@brian...

I am looking to average a range of numbers, but I want to exclude any cell
that has a zero from the Average. In this particular case, the zero's are
filler only and have no real meaning but they are distorting my figure.

I can't seem to find the magic formula to make it function.

Brian