Number of bedrooms calculation

[pierre robinson]

Hi All.

Apologies for the title - couldn't think how else to phrase it.

I have been given the following set of guidelines & can work out some of the variable, but am stumped around the code to work out mixed ages and sexes -
The Canadian National Occupancy Standard assesses the bedroom requirements of a household based on the following criteria:

• There should be no more than two persons per bedroom;
• Children less than 5 years of age of different sexes may reasonably share a bedroom;
• Children 5 years of age or older of opposite sex should have separate bedrooms;
• Children less than 18 years of age and of the same sex may reasonably share a bedroom; and
• Single household members 18 years or over should have a separate bedroom, as should parents or couples.

Here is where I am up to:


Thoughts?

Cheers

 

[CalcSux78]

Can't help but think about the Showtime series "Shameless"!

Pierre, Welcome to the forum. Would you be able to describe your layout? Maybe what kind of data is in each column, how many rows, etc? (In my signature, there's a link for the HTML Maker add-in for posting examples to the forum) or at a minimum, copy and paste row 1 to the thread.

 

[Eric W]

I spent a little time noodling on this problem. It's not terrible, but it is pretty difficult to come up with a concise formula. Here's how I approached it:

	A	B	C	D	E	F	G	H	I
1	Couples	Singles over 18		Girls >5	Boys >5	Girls<5	Boys<5		Minimum rooms
2	1	2		3	3	3	1		8
3			Pairs	1	1	1	0		8
4			Remainders	1	1	1	1
5
6	Index	Possible combinations of the remainders							How many rooms are needed
7	0			0	0	0	0		0
8	1			0	0	0	1		1
9	2			0	0	1	0		1
10	3			0	0	1	1		1
11	4			0	1	0	0		1
12	5			0	1	0	1		1
13	6			0	1	1	0		2
14	7			0	1	1	1		2
15	8			1	0	0	0		1
16	9			1	0	0	1		2
17	10			1	0	1	0		1
18	11			1	0	1	1		2
19	12			1	1	0	0		2
20	13			1	1	0	1		2
21	14			1	1	1	0		2
22	15			1	1	1	1		2

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D3 =INT(D2/2) E3 =INT(E2/2) F3 =INT(F2/2) G3 =INT(G2/2) D4 =MOD(D2,2) E4 =MOD(E2,2) F4 =MOD(F2,2) G4 =MOD(G2,2) I2 =A2+B2+D3+E3+F3+G3+VLOOKUP(D4*8+E4*4+F4*2+G4,A7:I22,9) I3 =A2+B2+D3+E3+F3+G3+CHOOSE(D4*8+E4*4+F4*2+G4+1,0,1,1,1,1,1,2,2,1,2,1,2,2,2,2,2) <tbody> </tbody>

<tbody>
</tbody>

In A2 put the number of couples, and in B2 the number of singles over 18. These all must have a room each. Now in D2 through G2, put the number of individuals that apply to each category. Now divide each number by 2 (row 3), and put the remainder in row 4. Now the numbers in row 3 must all get a room each, because 2 per room is maximum.

Now comes the tricky part. Depending on the remainders, we may require from 0 to 2 additional rooms, depending on age and gender. I compiled a list of every combination of remainders, and the number of rooms necessary. Using this, I actually created formulas that would calculate the number of rooms, but they were complicated. I decided that it was simpler to just look up the value. The formula in I2 does just that, it creates an index number from the remainders, then looks it up in the table. If you don't actually want the table, it can be incorporated into the formula, like in I3.

There's probably a neater way to do this, and if someone comes up with it, I'd like to see it.

Let us know if this is the kind of thing you're looking for.

 

[pierre robinson]

Can't help but think about the Showtime series "Shameless"!

Pierre, Welcome to the forum. Would you be able to describe your layout? Maybe what kind of data is in each column, how many rows, etc? (In my signature, there's a link for the HTML Maker add-in for posting examples to the forum) or at a minimum, copy and paste row 1 to the thread.

Thanking you.

At this point, what Eric W posted is about what I have - Im trying to give a bunch of tenancy managers a tool they can use to quickly calculate how many bedrooms are needed for a family.

Interdependant Adults	Dependant adults/subleasor	Male <=5	Female <=5	Male >5<=18	Female >5<=18	Aged or Disability	Number of Bedrooms reqd

<tbody>
</tbody>

And like I said, I can work out the vast majority of the code, its just the interdependence when children of different sexes and ages are mixed, based on that Canadian standard.

 

[pierre robinson]

Thanks Eric.

I am now going to have to work through your formula to figure out how it works

 

[Eric W]

Let me know if you have any questions. I've looked at it a few times since I posted, and it gets clearer the more you look at it. The tricky part is how to handle unpaired children of different ages and genders. Look at the 16 possible combinations I listed in rows 7-22 and convince yourself that the number of rooms needed makes sense. I came up with a slightly different way to look at it, but I'll save that if you have questions.

 

[pierre robinson]

Let me know if you have any questions. I've looked at it a few times since I posted, and it gets clearer the more you look at it. The tricky part is how to handle unpaired children of different ages and genders. Look at the 16 possible combinations I listed in rows 7-22 and convince yourself that the number of rooms needed makes sense. I came up with a slightly different way to look at it, but I'll save that if you have questions.

OK, its going to take a couple of days to work things through.
I got up to here:

IFERROR(SUM(ROUNDUP(A2/2,0),B2,(ROUNDUP((SUM(C2,E2,D2,F2)/2),0))),"0")&IF(G2="y","S","")

But it needs development around mixed typologies (and the S is for special i.e. aged or disabled)

All good fun until someone starts crying...

Cheers from New Zealand...