Bedroom Requirement

[robzrob]

I could probably work this out eventually, but my head is spinning and
I know some of you like a challenge. It will form part of a Housing
Benefit calculation. There's a figure used in the benefit calculation
(unimportant here) which is based on the bedroom requirement of the
household. I want the bedroom requirement.

The worksheet will have the age of the single adult in C5, the age of
the partner (if they exist) in D5, the ages of the children in E5 to
I5 and the sex of the children (m or f) in E6 to I6.

Bedroom requirement:

Single adult: 1
Couple (same or different sexes): 1
Single or couple with 1 child: 2
Single or couple with 2 or more children: 1 for the adult(s) plus 1
for each pair of children if they're both under 10 and of either sex,
2 for each pair of children if either are over 10 and they're
different sexes, 2 for each pair of children if either is over 16 and
of either sex.

 

[Dave]

Hi,
I was hoping someone else would answer this, but alas.
So far, I have this.
For 1 or 2 adults, always 1
For each kid 16 or over, regardless of gender, always 1
For 1 kid under 16, regardless of gender or under 10, always 1
For 3 kids under 16, regardless of gender or under 10, always 2
For 5 kids under 16, regardless of gender or under 10, always 3
For 2 kids under 16, usually 1, sometimes 2
For 4 kids under 16, usually 2, sometimes 3
I can do the formula for 2 kids under 16, but 4 kids under 16 is huge, and I
can't seem to simplify it, except by using lots of helper cells.
You made any progress?

Regards - Dave.

 

[robzrob]

Hi,
I was hoping someone else would answer this, but alas.
So far, I have this.
For 1 or 2 adults, always 1
For each kid 16 or over, regardless of gender, always 1
For 1 kid under 16, regardless of gender or under 10, always 1
For 3 kids under 16, regardless of gender or under 10, always 2
For 5 kids under 16, regardless of gender or under 10, always 3
For 2 kids under 16, usually 1, sometimes 2
For 4 kids under 16, usually 2, sometimes 3
I can do the formula for 2 kids under 16, but 4 kids under 16 is huge, and I
can't seem to simplify it, except by using lots of helper cells.
You made any progress?

Regards - Dave.

Nothing on paper, I've been hoping something will click into place
into my head! By helper cells, do you mean intermediate
calculations? Thanks for trying. I'm going to leave it for a while,
then come back to it.

 

[Dave]

Hi,
Helper cells, as E7:I7 would display any one of:
PF (primary female)
PM (primary male)
IF (intermediate female)
IM (intermediate male)
SF (senior female)
SM (senior male)

I was surprised to discover that any gender or age combination of 3 kids
under 16 always needed 2 bedrooms, and any gender or age combination of 5
kids under 16 always needed 3 bedrooms. This simplified things a lot, but
those with 4 kids under 16 is the toughie.
Regards - Dave.

 

[Ron Rosenfeld]

I could probably work this out eventually, but my head is spinning and
I know some of you like a challenge. It will form part of a Housing
Benefit calculation. There's a figure used in the benefit calculation
(unimportant here) which is based on the bedroom requirement of the
household. I want the bedroom requirement.

The worksheet will have the age of the single adult in C5, the age of
the partner (if they exist) in D5, the ages of the children in E5 to
I5 and the sex of the children (m or f) in E6 to I6.

Bedroom requirement:

Single adult: 1
Couple (same or different sexes): 1
Single or couple with 1 child: 2
Single or couple with 2 or more children: 1 for the adult(s) plus 1
for each pair of children if they're both under 10 and of either sex,
2 for each pair of children if either are over 10 and they're
different sexes, 2 for each pair of children if either is over 16 and
of either sex.

You need more accurate specifications.

For example, you have slots for 5 children but you are assigning bedrooms based
on "pairs". What do you do with a child who is in a "fractional" pair?

Also, you do not specify what you want to happen if a child is 10 (you specify
for over 10, and also for under 10).

I made some assumptions, but you should edit the formula if your facts are
different. I assumed that a 10 yr old would fall into the 10 to 16 group; and
that a 16 year old would be in the "over 16" group.

I also assumed that no child would be assigned to a 1/2 bedroom.

See if this works:

=================================
=SUM(OR(C55),
CEILING(COUNTIF(E5:I5,"<10")/2,1),
CEILING(SUMPRODUCT((E5:I5>=10)*(E5:I5<16)*(E6:I6="M"))/2,1),
CEILING(SUMPRODUCT((E5:I5>=10)*(E5:I5<16)*(E6:I6="F"))/2,1),
COUNTIF(E5:I5,">=16"))
================================

You may need to edit the equalities at 10 and 16 to match your actual
specifications.
--ron

 

[robzrob]

You need more accurate specifications.

For example, you have slots for 5 children but you are assigning bedroomsbased
on "pairs".  What do you do with a child who is in a "fractional" pair?

Also, you do not specify what you want to happen if a child is 10  (youspecify
for over 10, and also for under 10).

I made some assumptions, but you should edit the formula if your facts are
different.  I assumed that a 10 yr old would fall into the 10 to 16 group; and
that a 16 year old would be in the "over 16" group.

I also assumed that no child would be assigned to a 1/2bedroom.

See if this works:

=================================
=SUM(OR(C55),
CEILING(COUNTIF(E5:I5,"<10")/2,1),
CEILING(SUMPRODUCT((E5:I5>=10)*(E5:I5<16)*(E6:I6="M"))/2,1),
CEILING(SUMPRODUCT((E5:I5>=10)*(E5:I5<16)*(E6:I6="F"))/2,1),
COUNTIF(E5:I5,">=16"))
================================

You may need to edit the equalities at 10 and 16 to match your actual
specifications.
--ron- Hide quoted text -

- Show quoted text -

Your assumptions are what I wanted and I've tried various combinations
and it works so far. I'm astounded. Thank you very much.

 

[Dave]

Hi,
Does it work for 1 boy over 10 and 3 boys under 10?
I understood from your criteria that this would require 3 rooms:-
1 for the couple, 1 for 2 boys under 10, 1 for the under 10 boy and under 16
boy.
Have I misunderstood?
Dave.

 

[Ron Rosenfeld]

Hi,
Does it work for 1 boy over 10 and 3 boys under 10?
I understood from your criteria that this would require 3 rooms:-
1 for the couple, 1 for 2 boys under 10, 1 for the under 10 boy and under 16
boy.
Have I misunderstood?
Dave.

On re-reading the post, I believe your interpretation is correct, and that my
solution will not work for that case.
--ron

 

[robzrob]

On re-reading the post, I believe your interpretation is correct, and that my
solution will not work for that case.
--ron

Hello Ron & Dave

No it doesn't work for that combination - it gives 5, not 3. And it
gives 2 for 2 girls under 10, but 3 for 2 boys under 10!