AVERAGE

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!
 
D

David Biddulph

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

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

Teethless mama

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

you can shorten your formula
=AVERAGEIF(A1:A8 said:
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top