# 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

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:

#### 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