Averages excluding zeros
 

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?

Averages excluding zeros
 

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

Averages excluding zeros
 

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.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=561839

Averages excluding zeros
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=561839