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

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

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?

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

@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?

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

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

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?

Yes, @Kal_Lam! Thanks

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

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:

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)

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

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

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

1 Like