Averaging only the non-zero entries in a row?

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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
Click to expand...

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

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



--ron
 
Excellent! Just what I need! Thanks.

Ron Rosenfeld said:
Since you mention Excel 2007, one of the new functions is AVERAGEIF.

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



--ron
Click to expand...
 
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.
 
Dave said:
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.
Click to expand...


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