Calculation Based upon a number of conditions.

Hi

I need a magic formula for a property list to calculate if ref's numbers have to many bedrooms.

There is always one main resident, then the rest of the house is made up of their partner, children or other adults.

The attached workbook contains ref numbers, with following fields - no of bedrooms, total occupants, if there is a partner in the house (0 for no, 1 for Yes), then total children. then children broken down into their age groups.

The basic principle is how many bedrooms do they have over that they actually need.

This is the tricky bit, has to follow these conditions:

• 1 bedroom for the main resident and their partner.
• 1 bedroom for two children under 10 expected to share regardless of gender
• 1 bedroom for two children under 16 of the same gender will be expected to share

The answer/formula should go in the over occupation column (i.e. how many bedrooms extra do they have)

Really appreciate any help.

Thanks