Calculation and Conditional Statements

[AngelaBozeman]

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.

[AngelaBozeman · Accepted 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.

[JamesTedrick]

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.

[AngelaBozeman]

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.

[JamesTedrick]

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?

[AngelaBozeman]

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.