AVERAGE

  • Thread starter Thread starter Keith - NRCS
  • Start date Start date
K

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!
 
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")
 
In 2007
=AVERAGEIF(A1:A8,"<>0",A1:A8)
Click to expand...

you can shorten your formula
=AVERAGEIF(A1:A8 said:
In 2003
=AVERAGE(IF(A1:A8<>0,A1:A8,""))
Click to expand...

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

=AVERAGE(IF(A1:A8<>0,A1:A8))
 
Back
Top