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

I'm not really sure what you want...

Maybe this?

Formula:

Please Login or Register  to view this content.

This is number of how many they need.

You can add:

Formula:

Please Login or Register  to view this content.

P.S. No need to check partner since they have one room (number one in equation) no matter of partner or not.

Nearly there, however not correct.

i.e. ref 123548/M01. 2 beds. 1 main resident. and one child over 16. Your formula gives a result of 1. This doesn't make sense as 2 people that need a bedroom each. Could you have a look please.

That's because you have total children but you don't have their age...
M/F children is 0 in all other columns.