average calculation excluding 0 value

[#1]

Dear all,

I would like to calculate an average value on a questionnaire exluding the answers with the value 0 because this is linked to a non applicable item.

My quotation is :

CODE NOTATION	GB_SIGNIFICATION
0	Not Applicable to the company
1	Not done, but the subject has been identified
2	Done, but the local procedure has not been documented
3	Done and the local procedure has been documented but is not up-to-date or systematically applied
4	Done and the local procedure has been documented, updated, systematically applied and is familiar to all

How can I manage this ?

thanks in advance

christine

[joshabbott]

=avg(<CodeNotation-={'0'}> [number you are averaging])

This will act like a syntax error, but will work, if you don't want the syntax error try:

=avg(<CodeNotation={'*'} - {'0'}> [number you are averaging])

[#3]

Thanks Josh

but I am not sure to well undestand [number you are averaging]

in the exemple below, I would like to obtain a 2.2 resultat ( et not 1.6 )

can you explain more please

best regards

christine

question	ANSWER
A	0
B	2	2
C	4	4
D	3	3
E	0
F	1	1
G	1	1
.	1,6	2,2
.	AVERAGE	WITHOUT 0 VALUES

[joshabbott]

By putting the <CodeNotation-={0}> in the script, you are using set analysis.  In this situation, in your average calculation, you are excluding the 'CodeNotation' of 0 from your average calculation.  Hope that helps.  If you had 'Avg(Amount), it would give you the first column of answers above.  By adding the '<CodeNotation-={0}> that I supplied you, it should give you the second value for each question.

[antoniotiman]

Hi Christine,

I recommend You to use

Num(Sum(Answer)/Count({<Answer-={0}>} Question),'#.##0,0')

Regards

[#6]

Hi,

if its not considering 0  you can try sum(Answer) / count(Question)

[maxgro]

sum(ANSWER) / Count(if(ANSWER>0,ANSWER))