EXcluding Zeros from the average in a row
 

HI
I am trying to average a row of numbers (F35:U35) that have numeric zeros in
some of the cells. However, I would like to exclude them, and the cells from
the calculation "=AVERAGE(F35:U35)". Is there a way to do that?

Thanks
--
Geo

Hi Geo

This array formula will do the job:

=AVERAGE(IF(F35:U35<0,F35:U35))

To be entered with <Shift<Ctrl<Enter instead of <Enter,
also if edited later.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Geo" skrev i en meddelelse
...
HI
I am trying to average a row of numbers (F35:U35) that have numeric zeros
in
some of the cells. However, I would like to exclude them, and the cells
from
the calculation "=AVERAGE(F35:U35)". Is there a way to do that?

Thanks
--
Geo

In addition to Leo's array formula, you also have the
option of using:

=SUM(F35:U35)/COUNTIF(F35:U35,"<0")

so long as your range does not contain any empty cells or
non-numerical values.

HTH
Jason
Atlanta, GA

-----Original Message-----
HI
I am trying to average a row of numbers (F35:U35) that
have numeric zeros in
some of the cells. However, I would like to exclude them,
and the cells from
the calculation "=AVERAGE(F35:U35)". Is there a way to do
that?

Thanks
--
Geo
.

Empty cells are not equal to zero.

=SUM(F35:U35)/(COUNTIF(F35:U35,"0")+COUNTIF(F35:U35,"<0"))

is more bullet proof.

Jerry

Jason Morin wrote:

In addition to Leo's array formula, you also have the
option of using:

=SUM(F35:U35)/COUNTIF(F35:U35,"<0")

so long as your range does not contain any empty cells or
non-numerical values.

HTH
Jason
Atlanta, GA


-----Original Message-----
HI
I am trying to average a row of numbers (F35:U35) that

have numeric zeros in

some of the cells. However, I would like to exclude them,

and the cells from

the calculation "=AVERAGE(F35:U35)". Is there a way to do

that?

Thanks
--
Geo

I know empty cells are not equal to zero. That's why I
stated this formula is limited to non-numerical values and
and no empty cells. Your formula is definitely more bullet
proof in that regard.

Jason

-----Original Message-----
Empty cells are not equal to zero.

=SUM(F35:U35)/(COUNTIF(F35:U35,"0")+COUNTIF
(F35:U35,"<0"))

is more bullet proof.

Jerry

Jason Morin wrote:

In addition to Leo's array formula, you also have the
option of using:

=SUM(F35:U35)/COUNTIF(F35:U35,"<0")

so long as your range does not contain any empty cells
or
non-numerical values.

HTH
Jason
Atlanta, GA


-----Original Message-----
HI
I am trying to average a row of numbers (F35:U35) that

have numeric zeros in

some of the cells. However, I would like to exclude
them,

and the cells from

the calculation "=AVERAGE(F35:U35)". Is there a way to
do

that?

Thanks
--
Geo

.