Occupancy Rate / Percentage

[UFGATORS]

I'm trying to calculate the occupancy rate of a nursing home with 378 beds which would be 378*0.9 = 340.2. My problem I'm having is when there are 340 residents in the home Excel is indicating the occupancy rate of 95.95. Excel does not indicate the facility being at 90% until there are 341 residents in the home. If I don't use a decimal point I get a 90% occupancy rate, but I need to use two decimal points. Can someone explain why this is or is the formula I'm suing incorrect? The importance of this is when the facility is at 90% and a resident goes to the hospital we don't charge them to hold there bed. So when I see we have 340 residents on a particular day but not at 90% we would have to charge the resident. Thanks fo any assistance

 

[Scott Huish]

You didn't show your formulas. I assume 95.95 is a typo.
This is my best guess for what you need.

Book4
	A	B	C
1	# of beds	# of Residents	Occupancy Rate
2	378	340	90.00%
Sheet1

Cell Formulas
Range	Formula
C2	C2	=CEILING(B2/A2,0.01)

 

[UFGATORS]

Yes 95.95% is incorrect, 90.00% is what I meant. 340.2 is 90% of 378, my problem is when there are 339 residents the occupancy rate still indicates the facility is at 90.00%. On my spreadsheet, G800 contains the number of beds, G801 contains the number of residents needed to be at 90.00% i.e. (140) for this particular facility, L800 contains the occupancy rate for the day, the formula in L800 is =SUM(Q849,Q850,Q852)/($G800), Q849, 850, and 852 contain the totals of residents that are present in the facility for the day. L853 contains the total number of residents living in the facility regarless if they are present or not. So what needs to happen is if on any given day the number of total present veterans Q849, 850, and 852 is less the G801 should indicate the facility is less than 90.00% in L800. Does this make sense?

 

[Scott Huish]

So, how does my solution not help you?

=CEILING(SUM(Q849,Q850,Q852)/$G800,0.01)

 

[Dave Patton]

How are you supposed to round the calculation? You cannot have 20% of a person.

T202311a.xlsm
	A	B	C	D
1	# of beds	# of Residents	Occupancy Rate	> equal 90% or greater
2	378	340	89.95%	FALSE
3	378	341	90.21%	TRUE
4	378	340.2	90.00%	TRUE
5
4g

Cell Formulas
Range	Formula
C2:C4	C2	=B2/A2
D2:D4	D2	=(B2/A2)>=0.9
B4	B4	=A4*0.9