Average when value <> 0

[#1]

I want a function to calculate average, but only when the value is <> 0.

Numbers: 100, 0, 200

AVG(Number): 100 (100+0+200 / 3)
I want 100+200 / 2 = 150

Thank in advance!

[stephencredmond · Accepted solution]

Hi,

Use a set to exclude zero:

=Avg({< F1 -= {0}>} F1)

Stephen

[#3]

Use Set Analysis:

Avg({<Numbers = {'>0'}>} Numbers)

EDIT: Didn't see Stephen's reply when I posted. His will work for you.

[#4]

sum(Number) / count( if(Number>0,Number))

JJ

[stephencredmond]

Thanks for the question, it inspired a blog post:

http://qliktips.blogspot.com/2010/01/more-on-sets-or.html

BTW to the other guys - your ">0" set implies that all the numbers will be positive.

Stephen