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.
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
https://plus.google.com/u/1/103118600558151536014/posts/GqiR2wTSdF8
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โ," "))
User | Count |
---|---|
59 | |
25 | |
13 | |
12 | |
6 |