Hello. How can I use the Average function whe...

(Rogério Penna) #1

Hello. How can I use the Average function when one of the arguments PROBABLY will be either TEXT or LEFT BLANK?

I have this poll where user should be able to select a question.

Some questions will allow only 1 answer. Others will allow 3 answers.

All answers have the choices YES, NO or Not Applicable.

For score, YES should give 1. No should give 0 and Not Applicable means the question is not valid for that situation and thus should NOT BE COUNTED on the average.

Also, when selected question allows only 1 answer, only one answer will have the choices dropdown. The other answers are hidden and thus should remain empty.

I used 3 virtual columns to transform the answers into numbers. If YES becomes 1. If NO becomes 0, if Not Applicable becomes empty " ".

When I use average and a list, I expected the function to ignore text or empty cells. But it doesn´t.

So unless I fill all 3 answers with YES or NO (can´t use Not Applicable) I will get a NaN answers from the average function.

Of course, questions which hide two answer options, and thus remain empty cells, I can´t even use average because there is always two empty cells.

(Rogério Penna) #2

Tried this formula from Alex “([Dcto%]+[Dcto%.]+[Dcto%…]+[Dcto%…]+[Dcto%…])/ (IFS(ISNOTBLANK([Dcto%]),1)+IFS(ISNOTBLANK([Dcto%.]),1)+IFS(ISNOTBLANK([Dcto%…]),1)+IFS(ISNOTBLANK([Dcto%…]),1)+IFS(ISNOTBLANK([Dcto%…]),1))”

from this topic


However, it is considering all empty columns as 0!!

Here is the formula that transforms the Answer Columns (Yes, No or N/A) into 1, 0 or blank.

=if([Resp]=“Sim”,“1”,if([Resp]=“Não”,“0”," "))