EXcluding Zeros from the average in a row

[Guest]

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

 

[Leo Heuser]

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.

 

[Jason Morin]

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

 

[Jerry W. Lewis]

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]

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