Excel - Ensuring the difference is £5?

I think this in theory is a really simple one but its driving me mad ! lol.

Basically - looking at how you'd divide rents based on the cost/amount of people. Essentially, someone has said they will take the smallest room - if they pay £5 less a month, and the amount of people in the house is either going to be 3 or 4 - if its 4, the 2 in the smallest rooms should pay £5 less than those in the 2 bigger rooms (who pay the same).

Sounds really simple, but I can't get it to work reliably. So essentially.... dividing a total cost by either 3 or 4.... but ensuring the difference is always £5 in total :-|

Any help with a formula would be massively appreciated.

Looked at your spreadsheet but am a little confused.

you always have four rooms... but 3 or four may be rented. The small rooms need to be 5ukp less than the big (just for room) plus costs of utils (net and bills) shared between people...

The weekly rent is static, but the people may fluctuate? so you could have 2 small and 1 big or just two small but the weekly rent would need to stay the same..?

All of the houses we are looking at or 4 beds that will allow either 3 or 4 people.

So yes... always 4 rooms. Either 3 or 4 people.

Its the total per week (including bills) that needs the difference of £5.

Rent isn't static.... the different houses have different rental charges. But they all have the same set up of 2 nice rooms, and 2 crap rooms (damn houses).

Utils/bills would just be a straight divide?

Room rate would be % dependant depending on the rooms taken?

Have the attached so far... Just the amounts or % per room.

5ukp difference between types sorta blows the rental :/
More difficult than it looks.

where c2 is rent and b2 is number of people

for each small room =(C$2-5*(B$2-2))/B$2
for each large room =(C$2-5*(B$2-2))/B$2+5