Schedule on-call MDs random rotation based on availability

**Ten14** · 2013-07-06, 01:38 PM

I need to equally but randomly schedule list of 7 doctors into weekly call schedule that runs Tuesday thru Tuesday. Current manual version has doctors’ names one per cell across top row (D1 thru J1), and start date (A2 thru A27) then stop date (B2 thru B27) in 1st 2 columns. Next column is blank (c2 thru C27) to insert doctor name (formula I need), but ONLY if the word YES appears in the same row for that week opposite their name - starting with D2 thru J2 for the first week) (meaning they are available that week).

Doing this manually is so inefficient!

Hope I make sense. If there is a better way to set it up, I'm happy to accept any and all assistance as I only know basic Excel.

Thank you.

Just figured out I could attach my example...hope it is helpful in finding a fairly easy solution.

**shawnhet** · 2013-07-07, 04:40 AM

Hi there,

The following is a little clunky but should work. It does not choose members randomly but just in the same order as you list them after calculating how many times each has already worked and whether they are available this week.

First add an additional nine columns from K to Q with the same MD initials in row A.

Then in cell K3 post the following formula.

=IF(D3="YES",52-COUNTIF($C$1:$C2,D$1),0) and fill or copy across to column Q and then fill K3:Q3 down for the whole range

Then in C3 paste the following formula

=IF(MAX(K3:Q3)=K3,K$1,IF(MAX(K3:Q3)=L3,L$1,IF(MAX(K3:Q3)=M3,M$1,IF(MAX(K3:Q3)=N3,N$1,IF(MAX(K3:Q3)=O3,O$1,IF(MAX(K3:Q3)=P3,P$1,Q$1))))))

and fill down for your whole range.

Then all that is left to do is simply choose an MD for the first week by typing in their intials to C2.

I hope that helps. Let me know if you need some more info.

Cheers,

**Ten14** · 2013-07-08, 12:22 AM

Originally Posted by shawnhet

Hi there, The following is a little clunky but should work. It does not choose members randomly but just in the same order as you list them after calculating how many times each has already worked and whether they are available this week. First add an additional nine columns from K to Q with the same MD initials in row A. Then in cell K3 post the following formula. =IF(D3="YES",52-COUNTIF($C$1:$C2,D$1),0) and fill or copy across to column Q and then fill K3:Q3 down for the whole range Then in C3 paste the following formula =IF(MAX(K3:Q3)=K3,K$1,IF(MAX(K3:Q3)=L3,L$1,IF(MAX(K3:Q3)=M3,M$1,IF(MAX(K3:Q3)=N3,N$1,IF(MAX(K3:Q3)=O3,O$1,IF(MAX(K3:Q3)=P3,P$1,Q$1)))))) and fill down for your whole range. Then all that is left to do is simply choose an MD for the first week by typing in their intials to C2. I hope that helps. Let me know if you need some more info. Cheers,

Who cares about clunky when it WORKS!?!? Thanks very much...this is just what I needed to make life easier. I need to add 2 more call groups with 3 MDs each, but since they are easy I can do them manually for now. The main 7-doc ER call is what causes Excedrin headache #394 every 6 months. All I need to do now is decide if I should pretend I've slaved over this once again and soak up the praise...or fess up I was smart enough to get help. Hmmmm.... Thank you again, shawnhet!