charliemike9285
New Member
- Joined
- Jan 21, 2023
- Messages
- 12
- Office Version
- 365
- Platform
- Windows
- MacOS
- Web
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.
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) |
C3:L3 will compute the number of patients per staff member.
