I'm looking to create a formula that will do the following:

I have a column that I would like to get the average of but my problem is
that there are some cells with a 0(zero) in them. So the average must get
knocked down by taking the zero's into account.

My question is how do you create a formula to ignore the zero's but still
average the remaining numbers?

Thanks!

=AVERAGE(IF(A1:A10<0,A1:A10,"")) entered as an array formula (Control Shift
Enter)
--
David Biddulph

"Keith - NRCS" wrote in message
...
I'm looking to create a formula that will do the following:

I have a column that I would like to get the average of but my problem is
that there are some cells with a 0(zero) in them. So the average must get
knocked down by taking the zero's into account.

My question is how do you create a formula to ignore the zero's but still
average the remaining numbers?

Thanks!

Hi,

In 2007

=AVERAGEIF(A1:A8,"<0",A1:A8)

In 2003

=AVERAGE(IF(A1:A8<0,A1:A8,""))

This formula must be array entered - press Shift+Ctrl+Enter, instead of enter
or
=SUM(A1:A8)/COUNTIF(A1:A8,"<0")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Keith - NRCS" wrote:

I'm looking to create a formula that will do the following:

I have a column that I would like to get the average of but my problem is
that there are some cells with a 0(zero) in them. So the average must get
knocked down by taking the zero's into account.

My question is how do you create a formula to ignore the zero's but still
average the remaining numbers?

Thanks!

In 2007
=AVERAGEIF(A1:A8,"<0",A1:A8)

you can shorten your formula
=AVERAGEIF(A1:A8,"<0")

In 2003
=AVERAGE(IF(A1:A8<0,A1:A8,""))

The Null string at the end it doesn't do anything, you can omit that

=AVERAGE(IF(A1:A8<0,A1:A8))


"Shane Devenshire" wrote:

Hi,

In 2007

=AVERAGEIF(A1:A8,"<0",A1:A8)

In 2003

=AVERAGE(IF(A1:A8<0,A1:A8,""))

This formula must be array entered - press Shift+Ctrl+Enter, instead of enter
or
=SUM(A1:A8)/COUNTIF(A1:A8,"<0")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Keith - NRCS" wrote:

I'm looking to create a formula that will do the following:

I have a column that I would like to get the average of but my problem is
that there are some cells with a 0(zero) in them. So the average must get
knocked down by taking the zero's into account.

My question is how do you create a formula to ignore the zero's but still
average the remaining numbers?

Thanks!