Problems using Average

[MeritageSue]

I'm not sure if I should have replied to the old message
or re-posted. Here is the issue:

The non-contiguous range makes it more difficult.

One way: AVERAGE ignores most non-numeric values. So for your formulas in the
cells you wish to average, you could substitute the formula:

=IF(YourFormula>0,YourFormula,"")


--ron
.

Thanks Ron, I used your suggested formula in the cells I
want to average --having them return a value of "novalue"
if they are less than zero by using the following formula:
=IF(SUM(S23/160)>0,SUM(S23/160),"novalue")--and it is
working fine in those cells, but in the rollupo, it
doesn't seem to be making a difference the average does
not ignore those "novalue" cells when it tries to make the
average, it seems to still be counting them as 0 and
calculating those 0 cells those when figuring the
average. (one of the 12 cells has a numeric value of 25%,
the rest have a value of "novalue", the rolled up average
is shown as 2%)

Is there something I can use other than "novalue" that
would make them be ignored by the average function until
they have a true numeric value greater than 0? Or is there
some other function that will allow me to achieve the
average only of the cells with a value greater than 0?

Thanks!

 

[Ron Rosenfeld]

Thanks Ron, I used your suggested formula in the cells I
want to average --having them return a value of "novalue"
if they are less than zero by using the following formula:
=IF(SUM(S23/160)>0,SUM(S23/160),"novalue")--and it is
working fine in those cells, but in the rollupo, it
doesn't seem to be making a difference the average does
not ignore those "novalue" cells when it tries to make the
average, it seems to still be counting them as 0 and
calculating those 0 cells those when figuring the
average. (one of the 12 cells has a numeric value of 25%,
the rest have a value of "novalue", the rolled up average
is shown as 2%)

Is there something I can use other than "novalue" that
would make them be ignored by the average function until
they have a true numeric value greater than 0? Or is there
some other function that will allow me to achieve the
average only of the cells with a value greater than 0?

Thanks!

It IS easier if you reply in the same thread. Only chance led me to read this,
in which you provided more information than in your previous response.

I cannot reproduce your problem. AVERAGE should be ignoring the cells that are
displaying "novalue".

If you'd like, email me a copy of your worksheet. Change nospamorg to
direcwaycom with the period in the usual place.


--ron

 

[MeritageSue]

Ron,

My apologies. I've been messing around with it since my
post, and must have somehow fixed whatever was causing
that problem. Thanks to your input, it does seem to be
resolved now. I appreciate your assistance.

 

[Ron Rosenfeld]

Ron,

My apologies. I've been messing around with it since my
post, and must have somehow fixed whatever was causing
that problem. Thanks to your input, it does seem to be
resolved now. I appreciate your assistance.

You're welcome. Thanks for the followup.


--ron