Capping vulnerability scoring (creating a composite index)

Hi Everyone,

I am back again seeking assistance is capping calculations. I have to write an expression which would limit further calculation of vulnerabilities for households of more than 5 to obtain a max score of 15. Below is an explanation. I have highlighted rows 195-204 and rosw 250-252.

Criteria 3 Number of Disabilities - If a household member has 1 disability, they can score a point. If they have 2 or more disabilities the individual will score a max of 2 points. Each person has the potential of scoring a maximum of 3 points (2 for having 2 or more disability + an additional point if there are household members aged 5 or less or 65 and older). To limit the possible skewing of the scores based on household size, there is a cap of only calculating scores for up to 5 household members. Therefore, the maximum possible score is 3 points for disabilities multiplied by 5 household members = 15 points. So, if there are more than 5 members with each person the maximum points that can be scored having looked at age and disabilities remains 15. Note there is no age calculation on the primary applicant, but if they have disabilities this is scored and does form part of the scoring for that section. However, thereafter the score is divided by 3 to create a score out of 5. This is then multiplied by 2 for the final score for criteria 3.

cap.xlsx (82.1 KB)

Hi @scherrie,

Looking at your calculation it seems OK. So where did it break (at your end)?

Have a great day!

It broke for me where in rows 250 and 251 I set the cap where if((${household_total}>=5 and ${cum_weightedscore_allhhmembers}>=15),15,0).
Thing is I am uncertain if this calculation in takes into account if both the household total is less than 5 and cum weightedscore_allhhmembers is less than 15. I need the calculation to reflect all scenarios

Having reviewed your calculation below

It may not be reflecting what you intended as below

Your calculation simply ensures that for all total household member values equal or greater than 5 and all values were a cumulative weighted score for tall household members is equal to or greater than 15, a value of 15 is input otherwise a value of 0 is input.

Could you kindly confirm which scenario you wanted to effect.


1 Like

What i want is the following:

Primarily I want to limit households of larger sizes where persons are likely to have vulnerabilities and heighten the weighted score, than those with lesser membership if not capped.

Each additional member can obtain a max of 3 points (providing that they are age <=5 or >=65 (1 point). Additionally, each household member if they have 1 vulnerability will score 1 point, 2 or more will score 2. The primary applicant will only be able to score on vulnerabilities, not age.

So in essence I do not want the cummulative weighted age and vulnerability score for all household members to exceed 15. I wanted to limit scoring of a maximum of 5 household members. I think I may have to rethink that as this can also be impacted based on how the members are inputted.

1 Like

How can this be achieved?

How can this be achieved?

You may need to use multiple if conditionalities. e.g.

Unfortunately, this is a form specific support request which I suggest you post here and see if some user can pick it up.


1 Like

thanks for responding. I will try my best to understand your suggestion.