AVERAGE

[Keith - NRCS]

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!

 

[David Biddulph]

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

 

[Shane Devenshire]

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")

 

[Teethless mama]

In 2007

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

you can shorten your formula

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))