Sharing rooms - Formula brain fade

[obiron]

Hi guys,

This should be simple but its making my brain hurt...

I have four columns: Girls > 10, Boys >10, Girls <10, Boys <10

I am trying to calculate how many rooms are required.

The logic rules are as follows.

max 2 people to a room

Girls > 10 can share
Boys > 10 can share
A girl > 10 and a girl <10 can share
A boy >10 and a girl <10 can share
A boy >10 and a girl <10 can share

Example Results

[FONT=Courier New]G+  B+  G-  B-    Result  Reason[/FONT]
[FONT=Courier New]1   1   1   1     2       2 girls can share, 2 boys can share[/FONT]
[FONT=Courier New]1   0   0   1     2       Cannot share, different sex and one over 10[/FONT]
[FONT=Courier New]1   1   0   0     2       Cannot share, different sex and one over 10[/FONT]
[FONT=Courier New]0   0   1   1     1       Can share, both under 10[/FONT]
[FONT=Courier New]1   0   1   1     2       G- can share with either G+ for B-[/FONT]
[FONT=Courier New]1   0   2   1     2       one G- can share with G+, the other can share with B-[/FONT]

I am trying to build a forumla using FLOORs and MODs to calculate the correct number of rooms, but can't find a solution that covers all eventualities

can anyone help?

 

[obiron]

Done it!

assuming the four classifications are in A1:D1

=ROUND(A2/2,0) + ROUND(B2/2,0) + ROUND((C2+D2 - IF(C2>0,MOD(A2,2),0) - IF(D2>0,MOD(B2,2),0))/2,0)

Get the number of G+ rooms by dividing by 2 and rounding up
Get the number of B+ rooms by dividing by 2 and rounding up

Get the number of 10- rooms by adding the B- and G- together, but subtracting 1 from each IF there are any under 10s of the correct sex AND there is a space in a + room.