Hi Guys,
I am working on a tricky housing allocation exercise and struggling to find a proper formula. The story is the following:
As you can see in the attachment, I have got three tables:
1) Housing Entitlement policy matrix (A2:G6) to apply,
2) House portfolio (A12:B19), which includes certain house types ( e.g. 2 x 1 is a 2 bedroom, 2 bathroom) and total number of these houses available, and
3) Demographics of workforce entitled to housing allocation.
I need to come up with a formula in the blue cells (B25:G28), which would automatically calculate the number of houses available based on the housing entitlement policy with a preference to a smaller house a certain category may be entitled to. As an example, for the 70 “Singles”, first all available 1 x1 type of houses should be assigned (20), than all the 2 x 1 types (30) while for the remaining number of “Singles” (20) the 2 x 2 type of houses should be assigned. The remaining number of the 2 x 2 houses (40) should be taken up by the next category “Couples”.
The formula should work with “Yes” and “No” in the Housing Entitlement matrix in order to be able to pick up any potential change to it going forward (e.g. Should Singles become entitled to 3 x 1 type of houses too, by changing cell E3 to “Yes” the formula would re-distribute the numbers).
I would really appreciate your prompt answer and help.
Cheers
I am working on a tricky housing allocation exercise and struggling to find a proper formula. The story is the following:
As you can see in the attachment, I have got three tables:
1) Housing Entitlement policy matrix (A2:G6) to apply,
2) House portfolio (A12:B19), which includes certain house types ( e.g. 2 x 1 is a 2 bedroom, 2 bathroom) and total number of these houses available, and
3) Demographics of workforce entitled to housing allocation.
I need to come up with a formula in the blue cells (B25:G28), which would automatically calculate the number of houses available based on the housing entitlement policy with a preference to a smaller house a certain category may be entitled to. As an example, for the 70 “Singles”, first all available 1 x1 type of houses should be assigned (20), than all the 2 x 1 types (30) while for the remaining number of “Singles” (20) the 2 x 2 type of houses should be assigned. The remaining number of the 2 x 2 houses (40) should be taken up by the next category “Couples”.
The formula should work with “Yes” and “No” in the Housing Entitlement matrix in order to be able to pick up any potential change to it going forward (e.g. Should Singles become entitled to 3 x 1 type of houses too, by changing cell E3 to “Yes” the formula would re-distribute the numbers).
I would really appreciate your prompt answer and help.
Cheers