Count a filtered list but exclude the zeros

[clarcombe]

I am writing a macro to get an average for a filtered list. However, in order to calculate the correct average, I need to exclude the zeros in the list

So I have a filtered list like

17,82
16,92
17,28
17,46
16,74
17,46
17,1
17,46
0
0
0
0
0
0
0
0

so the average should be 138/8

How can achieve this
1) In Excel
2) In a macro.

Thanks

Colin

[Chris 53]

I put your data in B5:B19

=SUM(B5:B19)/COUNTIF(B5:B19,"<>0")

[AlKey]

Adjust ranges as needed

=AVERAGEIF(A2:A17,">0")

[clarcombe]

Hello,

Thanks for your replies. I think the AverageIf responds best to my needs.


Colin