Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
On Jun 23, 2:06*am, Dave wrote:
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
On Fri, 20 Jun 2008 16:05:20 -0700 (PDT), robzrob wrote:
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(C5:D5), 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
On Jun 23, 7:42*pm, Ron Rosenfeld wrote:
On Fri, 20 Jun 2008 16:05:20 -0700 (PDT), robzrob wrote: 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 thebedroomrequirement of the household. *I want thebedroomrequirement. 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. Bedroomrequirement: 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/2bedroom. See if this works: ================================= =SUM(OR(C5:D5), 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
On Mon, 23 Jun 2008 16:29:00 -0700, Dave
wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bedroom Requirement
On Jun 24, 2:40*am, Ron Rosenfeld wrote:
On Mon, 23 Jun 2008 16:29:00 -0700, Dave wrote: 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 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum with special requirement | Excel Worksheet Functions | |||
Fulfilling a sum requirement | Excel Discussion (Misc queries) | |||
formula requirement | New Users to Excel | |||
Requirement in Bar chart | Charts and Charting in Excel | |||
my bedroom | New Users to Excel |