Problems using Average

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

I'm rolling up data within a sheet, and I want it to
only
average numbers where there is a value greater than 0.
Currently it is returning an average of all 12 numbers
(including the items with a value of 0---so the average
is
the total of the numbers divided by 12). I want it to
only average the cells that have a value greater than
0.
Currently my formula is: =AVERAGE
(T22,T36,T50,T64,T78,T92,T106,T120,T134,T148,T162, T176)

I know there has to be an easy way to do this, but I'm
stumped.

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(YourFormula0,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!

On Sat, 31 Jan 2004 17:01:37 -0800, "MeritageSue"
wrote:

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

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.

-----Original Message-----
On Sat, 31 Jan 2004 17:01:37 -0800, "MeritageSue"
wrote:

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
.

On Sat, 31 Jan 2004 18:24:52 -0800, "MeritageSue"
wrote:

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