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)