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

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.

0 1 596
1 REPLY 1

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โ€," "))

Top Labels in this Space