Calculate using IF Formula with Multiple Expression (composite index/weighted score)

User Support Form Building
, , ,
1

Hi Colleagues,

Need your suggestion on how I develop calculation formula for this expression
Q.13 (select multiple question) with Possible Response:
1. Homemade toys, such as dolls or toy cars
2. Purchased dolls/toys
3. Household object such as crockery, pots, or items found outside the home such as twigs, stones, leather or leaves

(Scoring Tabulation)
1= score 3
(3 OR 2) AND 1= score 3
3 OR 2= score 2
Only 3 = score 2
Only 2 = score 1

Thanks in advance

2

Welcome to the community, @jsugiharto! So you have a multiple response question with say option 1, 2 and 3. Now you would like to add to a score to these through a different calculation question which adds the following score:

  • If option 1 is selected, the system gives a score of 3.
  • If option 3 or option 2 is selected with 1, the system gives a score of 3.
  • If option 3 or option 2 is selected, the system gives a score of 2.
  • If option 3 is selected, the system gives a score of 2.
  • If option 2 is selected, the system gives a score of 1.

Did I get it correctly?

3

Thanks for responding my query @Kal_Lam . Yes, you’re correct.

4

@jsugiharto, there seems to be a conflict between these 2 conditions:

  • If option 3 or option 2 is selected, the system gives a score of 2.
  • If option 2 is selected, the system gives a score of 1.

Could you make them clear?

5

As stated, this is a bit ambiguous (or inconsistent, as @Kal_Lam points out…). With 3 multi-select options, there are 8 permutations. Perhaps fill them in the desired values (called a truth table), from which a logical expression can be readily derived.

1 | 2 | 3 | Score
--+---+---+------
Y | N | N |
N | Y | N |
N | N | Y |
Y | Y | N |
Y | N | Y |
N | Y | Y |
Y | Y | Y |
N | N | N |

2 Likes
6

Ah, pardon my mistake @Kal_Lam. For the first one, it supposed to be: if “option 3 is selected with option 2” - so it would be an AND condition.
And lastly, if the respondent only choose option 2, so it will give score of 1

7

So your final conditions are as follows:

  • If option 1 is selected, the system gives a score of 3.
  • If option 3 or option 2 is selected with 1, the system gives a score of 3.
  • If option 3 and option 2 is selected, the system gives a score of 2.
  • If option 3 is selected, the system gives a score of 2.
  • If option 2 is selected, the system gives a score of 1.

Is this OK?

8

Yes, @Kal_Lam! Thanks

9

Thanks for pointing out through this truth table. Dr. @Xiphware

10

Well if this is your requirement, you could design your survey project using the following if-statement as outlined in the image shared below:

In the survey tab of your xlsform:

Image 1
Image 11211Ă—187 6.42 KB

In the choices tab of your xlsform:

Image 2

Data entry screen as seen in Enketo: If option 1 is selected, the system gives a score of 3

Output 1

Data entry screen as seen in Enketo: If option 3 or option 2 is selected with 1, the system gives a score of 3

Output 2.1

Data entry screen as seen in Enketo: If option 3 or option 2 is selected with 1, the system gives a score of 3

Output 2.2

Data entry screen as seen in Enketo: If option 3 and option 2 is selected, the system gives a score of 2

Output 3

Data entry screen as seen in Enketo: If option 3 is selected, the system gives a score of 2

Output 4

Data entry screen as seen in Enketo: If option 2 is selected, the system gives a score of 1

Output 5

Reference xlsform:

Composite Index.xlsx (13.1 KB)

11

unless you explicitly disallow this condition - namely the user doesnt selected anything - by using constraints, then your calculation still needs to generate a valid value for it (presumably 0?)

1 Like
12

For this truth table the simplest would be:

if(selected(${Q1}, 3, if(selected(${Q3}, 2, 1))

(assuming you dont care about N N N giving you 1…)

1 Like
13

Wow, It works . You’re super helpful! Thank you, @Kal_Lam

1 Like
14

Hello @Kal_Lam ,

I have the below question for scoring. I need the total of the selected options.

I followed the above instruction, but I do not know what is the mistake in my code.

Have you lost/damaged any of the following?
• Family member(s)/Relative(s) = 5 points
• Friend(s)/neighbour(s) = 5 points
• Furniture = 3 points
• Home appliance(s) = 3 points
• Car(s) = 3 points
• Other

Kindly find attached the xlsx form.
aQr3Z2B7AqksmfLkCfRPP9 (1).xlsx (7.2 KB)

Kindly advise,
Charbel

15

@Kal_Lam , I forgot to mention that I need the total of the selected choices.

Thank you