I need help in writing calculation expressions for the dependency ratio. The dependency ratio should be the number of unemployed persons divided by the number of employed persons. The primary applicant information is outside of the indexed repeat function.
Additionally, I also need to cap the total amount of vulnerabilities. A member of the household can attain a maximum of 3 points for vulnerability and age scoring. However, I want to cap the maxi scoring of this to 5 persons (5 *3) even if there are 5 or more persons in the house. Ho to write such a calculation.
Dependencyratio.xlsx (81.6 KB)
Attached is the xls sheet and the scoring matrix.
[Dependencyratio.xlsx|attachment]
(upload://iU1VFUAtemPpk0IyH6Z9qc1NraL.xlsx) (81.6 KB)
Hi,
Can you send the XLS form (it seems not attached) we look at it. In the meantime, have you looked at our advanced calculation article?
Stephane
1 Like
edited post. Xls file now appears. No I haven’t seen the calculate article but will review.
Could you take a stab at the article and then try this out first? Reach out in case you have any queries on how to fully get the calculation to work. In the meantime I will be looking at your XLS. Can you send along the write up of the analysis as a word document too?
1 Like
How do you attach word documents. Its not allowed in the file formats to upload
Can you paste the analysis approach as text or a PDF and see if it comes through
Based on the above, responses to the following questions will use to initially screen persons to be eligible for the programme:
(a) Immigration Status – BVI Citizen, Belonger, Resident, Work Permit Holder, Work Permit Exempt
(b) Employment Status prior to Mar 11 – Working full time, working part time having secured employment from July 1, 2019 to March 11, 2020
© Primary applicant must be aged 17-65
Once they meet the above, the following questions are to be scored and weighted:
(a) Criteria 1 Employment NOW -the scored responses would be working part time, unemployed covid, unemployed non covid. The individual score is then to be multiplied by 3 to determine their score. Maximum possible is 15.
(b) Criteria 2 Dependency Ratio – No. of non-working household members divided by no. of working household members. The primary applicant in addition to household members are used to calculate. For household members, any person who does not select (full time, part time) are then considered as non-working household members. The score derived after calculating the ratio is then multiplied by 3. Maximum possible score is 15.
© 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.
All eligible persons start with a minimum benefit award of $500. However, the benefit award is increased to the other two tiers $750 or $1000 based on the criteria 2 and 3 improving the scores to fall within the indicated point ranges commensurate with the tiers.
Please note I need to understand how to calculate:
The max scores specific to each criterion
How to cap the disabilities at minimum
Hi,
I took a look at the calculation article and attempted the calculations. The weighted score for disabilities for all household members is not calculating correctly. The cummulative total of disabilities for all members of the household is calculating correctly. If this doesn’t calculate correctly I cannot then add the additional calculations to finish the scoring. What follows is important to comprise the scoring calculation:
Primary applicant: if 1 disability score 1 point
if 2 or more disabilities score 2 points
no point scored on age
Household members: if member is <=5 score 1 point for age
if member is >=65 score 1 point for age
if member has 1 disability score 1 point
if member has 2 or more disabilities score 2 points
Member A who is the primary applicant is 40 and has 1 disability (should score 1 point for the disability
Eg. Member B is age 66 but has no disabilities (should score 1 point on age)
Member C is age 70 and has 3 disabilities (should score 1 point on age and 2 points on disabilities)
Member D is age 3 and has no disabilities ( should score 1 point on age.)
The results should be as follows
Total number of persons within the household with at least 1 disability = 2
Cummulative number of disabilities within the household = 4
Total weighted score for household = 6
Can you please check the calculations in the following rows to see where my error in writing the expression could be:
Rows 41-46; Rows 146-152 and Rows 170-177
scoring.xlsx (82.2 KB)
I also note that if I make changes in the repeat group of additional household members the changes are not reflected in the calculations(results are not adjusted to reflect the changes).
Is anyone able to assist?
Hi @scherrie
I have been looking at your flow and as I mentioned to you, normally there is a need to put the logic on “paper first” before you run a calculation. It normally brings a lot of efficiencies.
I have understood this part
Which rows actually show the weighted disability score.
Could you directly point out to your XLS form which ones are computing for each member description?
Stephane
