Formula help to calculate RN to Patient ratios based off certain conditions

[charliemike9285]

Can someone help with formulas that would help calculate the RN to Patient ratio depending on the Census, Total # of RNs, and # of IMU patients?
The green highlighted cells would be the ones that I would change based on the data.
The orange cells may or may not be used depending on the data (if i don't have a high enough census or acuity, then I don't need a 9th or 10th RN).

So, the conditions are:
1) The Charge would have a max of 2 patients, but could have less
2) there would be a max of 3 IMU pts per IMU RN, but also even if the number of IMU pts is not divisible by 3, the IMU RN would always only have 3 pts total (Ex. 1 IMU pt = 1 IMU RN w/ 3 pts ; 4 IMU pts = 2 IMU RNs w/ 3 pts each)
3) the regular RNs would have a max of 5 patients if there are any IMU patients and maxed at 6 patients if no IMU patients.

The problems I'm running into are:
1) I know there is probably a better formula to use than how I've written it so if there is please let me know
2) obviously, nurses can't have a "part" of a patient so how can I write the formula so it will essentially do the math in whole numbers
3) write the formula so that depending on how many IMU pts there are, the max ratio would be 3 for however many RNs.

Hopefully this makes sense but if not let me know and I will do my best to explain.

Staffing Ratios Problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		RN #	1	2	3	4	5	6	7	8	9	10			RN #	1	2	3	4	5	6	7	8	9
2		Census	Charge	RN 2	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9	RN 10			Census	Charge	IMU RN	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9
3	Total # of pts	36	2	4.85714	4.857	4.857	4.857	4.857	4.857	4.8571	#DIV/0!	#DIV/0!		Total # of pts	36	2	3.000	5.167	5.167	5.167	5.167	5.167	5.1667	#DIV/0!
4	# of IMU pts	1												# of IMU pts	1
5	Total # of RNs	8												Total # of RNs	8
6
7
8
9														Examples of how it should be with different scenarios:
10														Fully staffed, no IMU	RN #	1	2	3	4	5	6	7	8	9
11															Census	Charge	RN 2	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9
12														Total # of pts	36	2	4	5	5	5	5	5	5	N/A		36
13														# of IMU pts	0
14														Total # of RNs	8
15
16														Understaffed, no IMU	RN #	1	2	3	4	5	6	7	8	9
17															Census	Charge	RN 2	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9
18														Total # of pts	36	2	5	5	6	6	6	6	N/A	N/A		36
19														# of IMU pts	0
20														Total # of RNs	7
21
22
23														Full census, 4 IMU pts	RN #	1	2	3	4	5	6	7	8	9
24															Census	Charge	IMU RN	IMU RN	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9
25														Total # of pts	36	2	3	3	4	4	5	5	5	5		36
26														# of IMU pts	4
27														Total # of RNs	8
28
29														Low census, 2 IMU pts	RN #	1	2	3	4	5	6	7	8	9
30															Census	Charge	IMU RN	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9
31														Total # of pts	33	2	3	4	4	5	5	5	5	N/A		33
32														# of IMU pts	2
33														Total # of RNs	8
Sheet1

Cell Formulas
Range	Formula
D3	D3	=($B$3-$C$3)/($B$5-C1)
E3	E3	=($B$3-$C$3-$D$3)/($B$5-D1)
F3	F3	=($B$3-$C$3-$D$3-$E$3)/($B$5-E1)
G3	G3	=($B$3-$C$3-$D$3-$E$3-$F$3)/($B$5-F1)
H3	H3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3)/($B$5-G1)
I3	I3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3)/($B$5-H1)
J3	J3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3-$I$3)/($B$5-I1)
K3	K3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3-$I$3-$J$3)/($B$5-J1)
L3	L3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3-$I$3-$J$3-$K$3)/($B$5-K1)
R3	R3	=($O$3-$P$3-$Q$3)/($O$5-Q1)
S3	S3	=($O$3-$P$3-$Q$3-$R$3)/($O$5-R1)
T3	T3	=($O$3-$P$3-$Q$3-$R$3-$S$3)/($O$5-S1)
U3	U3	=($O$3-$P$3-$Q$3-$R$3-$S$3-$T$3)/($O$5-T1)
V3	V3	=($O$3-$P$3-$Q$3-$R$3-$S$3-$T$3-$U$3)/($O$5-U1)
W3	W3	=($O$3-$P$3-$Q$3-$R$3-$S$3-$T$3-$U$3-$V$3)/($O$5-V1)
X3	X3	=($O$3-$P$3-$Q$3-$R$3-$S$3-$T$3-$U$3-$V$3-$W$3)/($O$5-W1)
Z12,Z31,Z25,Z18	Z12	=SUM(P12:X12)

 

[charliemike9285]

I figured out how to round down (cell D3) so it calculates it in whole numbers (problem 2 listed above).
Any help with figuring out the other 2 problems would be greatly appreciated! Thank you!

Staffing Ratios Problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		RN #	1	2	3	4	5	6	7	8	9	10
2		Census	Charge	RN 2	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9	RN 10
3	Total # of pts	36	2	4	5.0	5.0	5.0	5.0	5.0	5.0	#DIV/0!	#DIV/0!
4	# of IMU pts	1
5	Total # of RNs	8
Sheet1

Cell Formulas
Range	Formula
D3	D3	=ROUNDDOWN(($B$3-$C$3)/($B$5-C1),0)
E3	E3	=($B$3-$C$3-$D$3)/($B$5-D1)
F3	F3	=($B$3-$C$3-$D$3-$E$3)/($B$5-E1)
G3	G3	=($B$3-$C$3-$D$3-$E$3-$F$3)/($B$5-F1)
H3	H3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3)/($B$5-G1)
I3	I3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3)/($B$5-H1)
J3	J3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3-$I$3)/($B$5-I1)
K3	K3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3-$I$3-$J$3)/($B$5-J1)
L3	L3	=($B$3-$C$3-$D$3-$E$3-$F$3-$G$3-$H$3-$I$3-$J$3-$K$3)/($B$5-K1)

 

[Snakehips]

@charliemike9285 Hopefully I have properly understood your conditions?
If so then maybe the below will help.
Maximum of 10 staff and 56 patients
I'm assuming that manual data entered in B3:B5 and also that the number of patients for the Charge is a manual entry in C3
You could use Data Validation to limit C3 to a max of 2 if you wish.
D2:L2 will auto populate with staff headings that match the numbers in B3:B5

C3:L3 will compute the number of patients per staff member.
If sum of patients does not equate to B3 then the range C3:L3 will conditional show as red indicating that the numbers B3:B5 create understaffing.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		RN #	1	2	3	4	5	6	7	8	9	10
2		Census	Charge	RN 2	RN 3	RN 4	RN 5	RN 6	RN 7	RN 8	---	---
3	Total # of pts	36	2	4	5	5	5	5	5	5	---	---
4	# of IMU pts	0
5	Total # of RNs	8
6
Sheet9

Cell Formulas
Range	Formula
D2:L2	D2	=IF(D1>$B5,"---",IF($B4>COUNTIF($C2:C2,"IMU RN")*3,"IMU RN","RN "&D1))
D3:L3	D3	=IF(D2="---","---",MIN(QUOTIENT($B3-SUM($C3:C3),$B5-C1),IF(D2="IMU RN",3,5+($B4=0))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:L3	Expression	=AGGREGATE(9,6, $C$3:$L$3)<>$B$3	text	NO

	RN #	1	2	3	4	5	6	7	8	9	10
	Census	Charge	IMU RN	IMU RN	RN 4	RN 5	RN 6	RN 7	RN 8	---	---
Total # of pts	36	2	3	3	5	5	5	5	5	---	---
# of IMU pts	4
Total # of RNs	8

	RN #	1	2	3	4	5	6	7	8	9	10
	Census	Charge	IMU RN	IMU RN	RN 4	RN 5	RN 6	RN 7	RN 8	RN 9	---
Total # of pts	36	2	3	3	4	4	5	5	5	5	---
# of IMU pts	4
Total # of RNs	9

HTH

 

[charliemike9285]

@Snakehips Yes, thank you! This is perfect! I still have so much to learn about how to get excel to do what I want!