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.
LinkedIn
Twitter
