Can anyone help?

  • Thread starter Thread starter Newdlj
  • Start date Start date
N

Newdlj

I have 9 people. I need 3 people per night during a 7 day week to wor
OT based on their availability. So if person 1 can only work Tuesda
and Wednesday, obviously they can't be chosen for the rest of the week
I need to be able to put people in a range and have the order chose
randomly for me based on their availability.

See data attached (these are people's availability) How can I have
people choosen for each day randomly? the RAND function only is usefu
for numbers - what about text

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=38896
 
Here's one way to set it up

(Post your email ID in reply to this thread if you would
like a copy of the completed file for reference)

In Sheet3:
------------

1. Create the named ranges listed below (via Insert > Name > Define)
-----------------------------------------------------------
a. For the lists of persons available per day of week
-----------------------------------------------------------
MonL =Sheet3!$A$2:$A$6
TuesL =Sheet3!$B$2:$B$5
WedL =Sheet3!$C$2:$C$7
ThursL =Sheet3!$D$2:$D$8
FriL =Sheet3!$E$2:$E$8
SatL =Sheet3!$F$2:$F$4
SunL =Sheet3!$G$2:$G$5

---------------------------------------------------------------
b. For the random #s to correspond to the # of persons
available per day of week
---------------------------------------------------------------
ListM =Sheet3!$A$10:$A$14
ListT =Sheet3!$B$10:$B$13
ListW =Sheet3!$C$10:$C$15
ListTh =Sheet3!$D$10:$D$16
ListF =Sheet3!$E$10:$E$16
ListS =Sheet3!$F$10:$F$12
ListSun =Sheet3!$G$10:$G$13

2. Select I10:I14
(this range corresponds to the Monday lists, viz.
the ListM range and to the MonL range)

Put in the formula bar: =RANK(ListM,ListM)

It's an array formula, so
Hold down Ctrl + Shift and press Enter
(instead of just pressing Enter alone)

Repeat the above steps to create likewise
for the other 6 days of the week, viz.:

Select J10:J13
Put in the formula bar: =RANK(ListT,ListT), array enter

Select K10:K15
Put in the formula bar: =RANK(ListW,ListW), array enter

Select L10:L16
Put in the formula bar: =RANK(ListTh,ListTh), array enter

Select M10:M16
Put in the formula bar: =RANK(ListF,ListF), array enter

Select N10:N12
Put in the formula bar: =RANK(ListS,ListS), array enter

Select O10:O13
Put in the formula bar: =RANK(ListSun,ListSun), array enter

3. Put in A10: =RAND()
Copy across to G10, then down to G16

4. Put in

I2: =INDEX(MonL,I10)
J2: =INDEX(TuesL,J10)
K2: =INDEX(WedL,K10)
L2: =INDEX(ThursL,L10)
M2: =INDEX(FriL,M10)
N2: =INDEX(SatL,N10)
O2: =INDEX(SunL,O10)

Select I2:O2
Copy down to row4

I2:O4 will give you a randomized OT schedule for Mon-Sun
according to the persons available per day of week

5. Put in Q1:Q10

Staff
DC
PH
JK
JA
EA
JN
KS
CL
LP

(the above is the list of the 9 persons)

Put labels in:

R1: Scheduled OTD#
S1:Available OTD#
T1: OT Utilization %

Put in R2: =COUNTIF($I$2:$O$4,Q2)
Put in S2: =COUNTIF($A$2:$G$8,Q2)
Put in T2: =R2/S2
Format T2 as %

Select R2:T2
Copy down to row10

6. Press F9 to regenerate a random schedule.

Look at the figs in col T (OT Utilization %)

If there are say, no zero percentages anywhere
then the particular randomized schedule is quite “equitable”
in that every one of the 9 persons is scheduled for some OT during the week
(assuming this is also part of the consideration in the scheduling)

If zero(es) appear, press F9 again to regenerate another random schedule
and re-check the percentages in col T.

Repeat until you’re happy with the results.

Use copy > paste special > values to freeze the random schedule
in I1:O4 somewhere else
 
(e-mail address removed)

Thanks Max for the help. My email is for the reference file.

I appreciate it.
 
Back
Top