I am looking to sum the total of multiple questions within my survey and divide by the number of questions answered but the problem is the answer can also be N/A. I figured out a way to get the total number of N/A and subtract that number from the total # of questions to get the number to divide by.
Example: If there are 5 questions total my equation looks like
5 - (if(selected(${question1}, 'NA', 1, 0) + if(selected(${question2}, 'NA', 1, 0)....repeated for each question)
This portion works fine. The issue is I haven't found a way to sum the values where there is a non-numeric value in the mix. When using a simple addition equation the answer always = 0 when any of the questions are answered N/A.
I tried using an if statement: if(selected(${question1}, 'NULL'), 0, ${question1}) to make the N/A value = 0
This works for each question individually but when I try to combine this line for all 5 questions by using a comma between it doesn't work. I also tried using parenthesis around each equation and one around the entire group.
The calculation I am ultimately trying to achieve can be seen in the attachment.
Please help and let me know if there is a better way to accomplish the same task. Thanks in advance.