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.6 KB)
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.
