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

On Mon, 7 Apr 2008 09:01:54 -0400, "Ken"
wrote:

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

Excellent! Just what I need! Thanks.

"Ron Rosenfeld" wrote in message
...
On Mon, 7 Apr 2008 09:01:54 -0400, "Ken"
wrote:

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

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" wrote in message
...
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.

--
DB.