Formula to calculate # of single vs double rooms

[Officestar]

 

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.

[#2]

@Officestar 

Perhaps

E8:
=INT( E4*B8 )

E7:
=E4-E8

[#3]

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.

I am also focussing finding the number of  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. 

[#4]

@Officestar 

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

[#5]

Hi Sergei, getting there.

So D = 2*S
D+S = 94 and 2*S+S=94
And 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.

[#6]

@Officestar 

Sorry, I missed. "total number of beds which again is a variable", so 94 is not fixed number of available beds, that is number of booked beds, or so? 

In general, what is input and what is desired output?

[#7]

 

@SergeiBaklan 

Hi Sergei, so the % would be input, and also total number of beds (94) is an input too. I want the ability to adjust either of the two get the number of rooms needed.

So I finally figured it out:

if S = Single Occupancy %
and D = Double Occupancy % 

E6 =INT((E2/(B5+B6+B6))*B6)

E5 =E2-(E6*2)

had to divide the 94 by adding S+two times D to get the equal part of 94 beds and then multiply it by D to get the actual double occupancy room count. I hope that makes sense

[#8]

@Officestar 

When you did the job, thank you for the clarification. I'd suggest to use two inputs - number of beds and % of Double, assuming S+D is always 100%. When the logic is

S+2D=Beds

D= D%*(D+S)

From here

D=Beds*D%/(1+D%)

or in E6

=INT(E2*B6/(1+B6) )

It's always the rounding and yes, it's more reasonable to trunk number of double rooms.