Formula to calculate # of single vs double rooms

Question

Hi, so I’ve been struggling to find a formula to calculate the number of single occupancy room vs double occupancy by just adjusting the % of either the single or double occupancy to the total number of rooms, based off the total number of beds.

So say I have hotel with 94 beds in total and I want to figure out what is the number that will be be double vs single if I want to keep the proportion of double occupancy at 60%.

S = Single Occupancy

D = Double Occupancy

S+D = 94

D also = 2*S
and D is 60% of (S+D)

again I want to be able to control the % to spit out the actual room count based of the adjustable total number of beds (94 in the example above) shown in yellow box in image.

Need formulas for the green boxes. Thanks.

sergeibaklan · Answer

Officestar&nbsp;
Perhaps

E8:
=INT( E4*B8 )

E7:
=E4-E8

officestar · Answer

Hi Sergei, thanks for the reply.The 40-60% is single vs double rooms hence on the total number of room count not the the bed count.&nbsp;I am also focussing finding the number of &nbsp;double rooms first, as it is going to be a fraction and I will round it up and then from there get the number of single occupancy rooms. It’s this first step I’m stuck at.&nbsp;

sergeibaklan · Answer

Officestar&nbsp;
Sorry, my bad. So
2*D + S = 94
D = 0.6*(D+S)
From that
S = 4/6D and
D = 94*6/16
or rounding
E8:
=INT( E4*6/16 )

E7:
=E4-2*E8

officestar · Answer

Hi Sergei, getting there.So D = 2*SD+S = 94 and 2*S+S=94And D also = x% * (D+2*S)X% is what I want the ability to change in a different cell to give me the outcome of D out of the total number of beds which again is a variable I want to control.I want the ability to see a change the percentages of the double rooms to see if it’s 60% or 50% or 40% double occupancy what will be the number of double rooms I will need, if it’s a 80 or 90 or 100 bed hotel. I’m just using this as an example.I got lost when you came up with 6/16 factor, I would like it to relate to the % column as a formula.

sergeibaklan · Answer

Officestar&nbsp;
Sorry, I missed. "total number of beds which again is a variable", so 94 is not fixed number of available&nbsp;beds, that is number of booked beds, or so?&nbsp;
In general, what is input and what is desired output?

Forum Discussion

Formula to calculate # of single vs double rooms

Share

Resources