AVERAGE

[Connie Martin]

How do I average a column of numbers to exclude zeros? If there are 3 zeros
and 2 numbers, then the average function is dividing the two numbers by 5. I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then the
average I want is 2.6 because it would only average the numbers above zero.
Thank you. Connie

 

[Barb Reinhardt]

= AVERAGE(IF(A1:A5<>0,A1:A5))
Commit with CTRL SHIFT ENTER

 

[T. Valko]

Try one of these:

Excel 2007 only:

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

All versions of Excel array entered** :

=AVERAGE(IF(A1:A5<>0,A1:A5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Connie Martin]

Sorry, the array formula is giving me #DIV/0! and I pressed CTRL, SHIFT &
ENTER. What the problem be? Is there an add-in I need? Connie

 

[Connie Martin]

Sorry, I'm getting #DIV/0! and I pressed CTRL SHIFT ENTER after entering it.
Is there an add-in I need or something? Why doesn't it work? Connie

 

[Connie Martin]

Sorry, my mistake!! This DOES work! Thank you very much. Connie

 

[Connie Martin]

Sorry, my mistake!! This DOES work! Thank you! Connie

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Sorry, my mistake!! This DOES work! Thank you! Connie