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.
Solved! Go to Solution.
I found a way to make this calculation work. It may not be the best way, but I obtain the desired result.
I used:
if(selected(${question1}, 'NA'), 0, number(${question1})) + if(selected(${question2}, 'NA'), 0, number(${question2}).....repeat for all questions)
I can then divide by the number calculated in the working function above.
I found a way to make this calculation work. It may not be the best way, but I obtain the desired result.
I used:
if(selected(${question1}, 'NA'), 0, number(${question1})) + if(selected(${question2}, 'NA'), 0, number(${question2}).....repeat for all questions)
I can then divide by the number calculated in the working function above.
Hi Angela,
Can you describe how your choice list looks? In particular, is 0 a valid value for people to enter? If not, I would reassign 'N/A' to 0 - having all numeric answers would make what you're trying to do easier, I think.
Valid entries are 0-3 in intervals of 0.5. So unfortunately assigning a value of 0 will not work. This is why I need a N/A option.
Yes, I was guessing this might be the case. In re-reading through your description above, I'm not quite sure what you mean by "when I try to combine this line for all 5 questions by using a comma between it doesn't work." If you're trying to combine the if(selected()) together, you would want to add them together, correct?
In my original question I thought I needed to specify for each question if the response is N/A then designate that value as 0 (using commas between if statements) before adding the values together. I then found the best way to accomplish this task was to combine it all into one if statement if(selected(${question1}, 'NA'), 0, int(${question1})) + additional questions.
One additional note is that since I'm using number 0-3 in 0.5 increments instead of converting to an integer int(${question1}) my list values should be converted to a number. number(${question1}).
I will adjust my answer accordingly.