SOLVED
Home

Calculate a percentage increase that doesn't exceed 100%

%3CLINGO-SUB%20id%3D%22lingo-sub-394076%22%20slang%3D%22en-US%22%3ECalculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394076%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20am%20currently%20working%20on%20an%20occupancy%20rate%20up%20model.%20Here%20is%20the%20scenario%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20hotel%20with%20occupancy%20rate%20of%2070%25%20and%20ramp%20up%206%20months%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20calculate%20the%20EOM%20occupancy%20rate%20and%20average%20occupancy%20rate%26nbsp%3B%3C%2FP%3E%3CP%3EEOM%20occupancy%20rate%20%3D%2070%25%2F6%20%3D%200..7%2F6%20%3D%200.1167%20%3D%2011.67%25%3C%2FP%3E%3CP%3Ewhile%20the%20average%20rate%20%3D%20(previous%20EOM%20occupancy%20rate%20%2B%20current%20EOM%20occupancy%20rate)%2F2%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20a%2010%20year%20plan%2C%20with%20the%20above%20function%2C%20the%20values%20will%20exceed%20100%25%20and%20in%20reality%20an%20hotel%20won't%20have%20150%25%20occupancy%20rate.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20modify%20the%20function%20so%20that%20when%20EOM%20occupancy%20rate%20reaches%2095%25%2C%20it%20remains%20there.%20It%20won't%20exceed%20100%25%20even%20the%20plan%20is%20100%20years.%20Thank%20you%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394076%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394700%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394678%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%20I%20design%20a%20model%20for%20hotel%20occupancy.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20minimum%20value%20of%20occupancy%20rate%20is%20usually%2050-70%25%20while%20the%20ramp%20up%20is%20usually%206%20months.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20challenge%20with%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20put%20the%20value%20as%20occupancy%20rate%20%3D%2050%25%2C%20the%20forecast%20rate%20stops%20at%2050%25%20...%20The%20proposed%20design%20is%20to%20take%20100%25%20then%26nbsp%3B%3DMIN(D13%2C%201)%20keeps%20it%20at%20100%25.%20I%20attached%20a%20file%20for%20more%20clarity.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394349%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394349%22%20slang%3D%22en-US%22%3EWhat%20are%20the%20maximum%20values%20for%20each%20of%20those%202%20assumptions%3F%20Regardless%20of%20what%20they%20are%2C%20the%20EOM%20occupancy%20rate%20will%20always%20be%20less%20than%20100%25.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394127%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%20This%20looks%20okay%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394126%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%20Much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20however%20there%20is%20an%20provision%20for%20business%20analysts%20to%20change%20the%20occupancy%20rate%20as%20well%20as%20occupancy%20ramp%20values...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20suggestion%20regarding%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394120%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394120%22%20slang%3D%22en-US%22%3EBased%20on%20your%20formula%2C%20the%20EOM%20occupancy%20rate%20will%20be%20limited%20to%20a%20maximum%20of%2016.67%25%2C%20which%20is%20100%25%2F6.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394109%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20percentage%20increase%20that%20doesn't%20exceed%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313611%22%20target%3D%22_blank%22%3E%40AKP_1%3C%2FA%3E%20%2C%20perhaps%3C%2FP%3E%0A%3CPRE%3E%3DMIN(%26lt%3BEOM%20formula%26gt%3B%2C%200.95)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
AKP_1
Occasional Contributor

Hello everyone,


I am currently working on an occupancy rate up model. Here is the scenario

 

I have a hotel with occupancy rate of 70% and ramp up 6 months 

 

I need to calculate the EOM occupancy rate and average occupancy rate 

EOM occupancy rate = 70%/6 = 0..7/6 = 0.1167 = 11.67%

while the average rate = (previous EOM occupancy rate + current EOM occupancy rate)/2 

For a 10 year plan, with the above function, the values will exceed 100% and in reality an hotel won't have 150% occupancy rate. 

 

I need to modify the function so that when EOM occupancy rate reaches 95%, it remains there. It won't exceed 100% even the plan is 100 years. Thank you 
 

 

7 Replies
Solution

@AKP_1 , perhaps

=MIN(<EOM formula>, 0.95)

 

Based on your formula, the EOM occupancy rate will be limited to a maximum of 16.67%, which is 100%/6.

@Twifoo 

 

Thank you for your reply. Much appreciated!

 

Yes however there is an provision for business analysts to change the occupancy rate as well as occupancy ramp values...

 

Do you have any suggestion regarding that?

@Sergei Baklan 

 

Thank you for your response. This looks okay

What are the maximum values for each of those 2 assumptions? Regardless of what they are, the EOM occupancy rate will always be less than 100%.

@Twifoo 

 

Thank you for your response. I design a model for hotel occupancy. 

 

The minimum value of occupancy rate is usually 50-70% while the ramp up is usually 6 months.

 

I have a challenge with this formula

 

If I put the value as occupancy rate = 50%, the forecast rate stops at 50% ... The proposed design is to take 100% then =MIN(D13, 1) keeps it at 100%. I attached a file for more clarity. 

 

 

Regards

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies