Aug 15 2023 11:00 PM - edited Aug 15 2023 11:04 PM
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.
Aug 15 2023 11:49 PM
Aug 16 2023 08:47 AM - edited Aug 16 2023 09:02 AM
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.
Aug 16 2023 09:23 AM
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
Aug 16 2023 01:28 PM
Aug 18 2023 04:23 AM
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?
Aug 18 2023 03:15 PM
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
Aug 19 2023 03:21 AM
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.