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