Forum Discussion
Formula to calculate # of single vs double rooms
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
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.
- SergeiBaklanAug 18, 2023MVP
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?
- OfficestarAug 18, 2023Copper Contributor
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
- SergeiBaklanAug 19, 2023MVP
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.