Averaging only the non-zero entries in a row?

[Ken]

Office Excel 2007

Is there a way to find the average of column of figures EXCLUDING any row
that has a zero in it?

I have a column of figures. Each row corresponds to a day, and each day a
new row is added. Some rows have a zero value entered, while the others have
a non-zero number.

Finding the average of the entire row is, I know, easy. =AVERAGE(A1:A100),
for example. However, is there an easy way to exclude cells within the
A1:A100 range that have a zero, so that the I get the sum of the non-zero
rows, divided by the number of rows with non-zero entries?

Many thanks.

Ken Isaacson
SILENT COUNSEL, a legal thriller
www.KenIsaacson.com

 

[Ron Rosenfeld]

Office Excel 2007

Is there a way to find the average of column of figures EXCLUDING any row
that has a zero in it?

I have a column of figures. Each row corresponds to a day, and each day a
new row is added. Some rows have a zero value entered, while the others have
a non-zero number.

Finding the average of the entire row is, I know, easy. =AVERAGE(A1:A100),
for example. However, is there an easy way to exclude cells within the
A1:A100 range that have a zero, so that the I get the sum of the non-zero
rows, divided by the number of rows with non-zero entries?

Many thanks.

Ken Isaacson
SILENT COUNSEL, a legal thriller
www.KenIsaacson.com

Since you mention Excel 2007, one of the new functions is AVERAGEIF.

So: =AVERAGEIF(A1:A100,"<>0")



--ron

 

[Ken]

Excellent! Just what I need! Thanks.

Since you mention Excel 2007, one of the new functions is AVERAGEIF.

So: =AVERAGEIF(A1:A100,"<>0")



--ron

 

[Dave]

Hi,
By the way, in XL 2000 (and maybe other versions) I have found that AVERAGE
ignores blank cells when working on a range of cells. However, if a cell
value is 0 (zero) then it is included in the calculation, and affects the
result.

 

[DB.]

Hi,
By the way, in XL 2000 (and maybe other versions) I have found that
AVERAGE
ignores blank cells when working on a range of cells. However, if a
cell
value is 0 (zero) then it is included in the calculation, and affects
the
result.

Yes, I'm finding that Excel 2003 behaves like that, too.