Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Averages but excluding zero's | New Users to Excel | |||
Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
Average, Excluding Zeros, Non-Consecutive Range | Excel Discussion (Misc queries) | |||
Average non continguous cells, excluding zero's | Excel Worksheet Functions | |||
EXcluding Zeros from the average in a row | Excel Discussion (Misc queries) |