Forum Discussion
Officestar
Aug 16, 2023Copper Contributor
Formula to calculate # of single vs double rooms
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 tot...
Officestar
Aug 16, 2023Copper Contributor
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.
SergeiBaklan
Aug 16, 2023MVP
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
- OfficestarAug 16, 2023Copper ContributorHi 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.- 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