Averages excluding zeros

[Guest]

Is there a way to average a column of numbers, but have Excel ignore "0"s?
For example if my column is:
2
3
4
0
1
0
Excel will average this as 1.67 (sum of 10 divided by 6 entries) , but what
I need is 2.5 ( Sumof 10 divided by 4 non-zero entries.)
Bonus Question: Is there a way to create this effect in a Pivot Table?

 

[vezerid]

Jaytee,

Assuming your data are in A1:A6,

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

This is an *array* formula, hence it should be committed with
Shift+Ctrl+Enter

HTH
Kostis Vezerides

 

[mrice]

I suggest using the following user defined function

Function AverageWithOutZeros(DataRange As Range)
For Each Cell In DataRange
If Cell <> 0 Then
Total = Total + Cell
Count = Count + 1
End If
Next Cell
If Count <> 0 Then
AverageWithOutZeros = Total / Count
Else
AverageWithOutZeros = "#N/A"
End If
End Function

Paste this into a macro sheet and it should then be available on the
insert function menu item.

 

[daddylonglegs]

If you don't have negative values

=SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,">0"))

or

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

confirmed with CTRL+SHIFT+ENTER