Averages excluding zeros

Jaytee · 07-16-2006, 11:20 AM

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 · 07-16-2006, 11:25 AM

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** · 07-16-2006, 11:26 AM

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.