Randbetween function with restricted conditions

[Simeonb]

Hey Excel community,

I have come accross a problem that I cannot overcome and I am wondering whether a macro is the only way to solve this problem. I hope not since I don't know much about macros.

I am trying to make an automatic job schedule with the following availability options from different persons:

excel picture1.PNG

I came this far as that I select all the ''yes'' options per date and pick a randomized person that is able to work on that date. To create some equality between the employees I want to 'force' the randbetween to atleast schedule a person once per two weeks and a maximum number of day parts which they can fill in themselves (see row 29). However the randbetween is not as simple as picking a number between 1 and the maximum number of every employee as you can see in the following image, because the other randbetween already has to figure out which random person has to work on a specific day. How can I restrict the randbetween in this formula so that the sum over these two weeks is between 1 and max amount chosen by employee? Someone knows how to adjust the formula?

excel picture2.PNG

Thankyou very much. Preciate it.

[CAntosh]

Welcome to the forum!

RANDBETWEEN doesn't work well with these sorts of restrictions, since it doesn't have the "intelligence" to plan ahead to make sure everybody gets a chance. I've been poking at this for a few days, though, and I think I have a solution that might be as close as you can get. For each day, the formula below looks at who is available, what their maximums are, how many days they've worked already, and how many days of availability they have left. In short, it selects the available person with the smallest window of available:maximum days remaining, without going over the max. When there is a tie for a particular day, it randomly chooses between the tied people - with your current data, the random factor only comes into play for about 4 or 5 days.

As I said, however, it's not perfect. The availability can be poorly stacked, or the random choices could make someone necessary unavailable at the end of the month - in your attachment, person 3 is the only available person for the last day, and is only under the max at that point in half of my simulations. Fortunately, all you need to do is to hit the F9 key to re-randomize the RANDBETWEENs until you get a distribution that fills every day.

Ultimately, it's not perfectly random, but it uses an element of randomness while still attempting to account for everyone's restrictions.

The formula below goes in B34. It should be array-confirmed (Ctrl + Shift + Enter instead of just Enter):

=IFERROR(INDEX($G34:$O34,1,INDEX(SMALL(IF(IF((LEN($G34:$O34)>0)*($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32)>0),(COUNTIF($G34:$O$59,$G34:$O34)-($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32))))=SMALL(IF((LEN($G34:$O34)>0)*($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32)>0),(COUNTIF($G34:$O$59,$G34:$O34)-($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32)))),1),COLUMN($G34:$O34)-COLUMN($G34)+1),{1,2,3,4,5,6,7,8,9}),1,RANDBETWEEN(1,SUMPRODUCT(--ISNUMBER(SMALL(IF(IF((LEN($G34:$O34)>0)*($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32)>0),(COUNTIF($G34:$O$59,$G34:$O34)-($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32))))=SMALL(IF((LEN($G34:$O34)>0)*($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32)>0),(COUNTIF($G34:$O$59,$G34:$O34)-($B$29:$J$29-COUNTIF($B$33:$B33,$G$32:$O$32)))),1),COLUMN($G34:$O34)-COLUMN($G34)+1),{1,2,3,4,5,6,7,8,9})))))),"")

To make things easier, I've added the following formula in C34 to identify when re-randomization needs to be done. If any cell says "PROBLEM, hit F9 key", hit F9 to recalculate/rerandomize until everything is good.

=IF(F34=0,"Nobody Available",IF(B34="","PROBLEM, hit F9 key","Good"))

Feel free to experiment with the attachment to see if it'll do:

[Simeonb]

CAntosh, amazing!

It's not flawless, but I can tell you put a lot of effort in it and I appreciate it very much.

Maximum day parts confuses the formula. Some rows 'refuse' to randomise. If I change maximum day parts it starts randomising again.

I'll try work a around a few minor problems but this is very much usable. Thanks sir

[CAntosh]

I'm glad I could help!

By design, the maximum day element will often prevent true randomization, since the formula first looks to see which of the available workers has the fewest opportunities remaining to fit their days in. It only randomizes when there is a tie in those results. In the attachment, I think the randomization only figures in with about 4 days. Removing the maximum days criteria from the formula will offer more randomization, but it's far less likely to properly fill the days according to everyone's criteria; on the last few days the people available will all be exceeding their maximums. If you've got a lot of people available for each day, then you can probably get away with removing the max. days clauses and having more true randomization. Based on the sample, though, I had a hard time getting those last days filled without including the max clause, which attempts to look forward at what opportunities remain. Based on your real data, you'll have to figure out which balance works best for you. Good luck!

[Simeonb]

Sorry to bother again. I am not sure if you will get a notification of this message but it would be nice if you did. The project has stood still and today I have tried to remove the Max clause and just stick with a minimum of 1 day part per person so I can just fairly press F9 until I get the desired result. I have tried to modify the formula, but since it's creepy long I am not sure how to. I do know that it will be much more simple to program a minimum of 1 per person, but I still lack the knowledge on how to do it for this project. It would be wonderful if you could have a look at it again. If it's to much work, please don't bother

Thanks in advance

Simeon

[CAntosh]

Removing one of the criteria opens up the door to more randomness. I think the formula below, array-entered (confirm with Ctrl + Shift + Enter) in B34 and filled down, will do the trick:

=INDEX($G34:$O34,1,IF(SUMPRODUCT((LEN($G34:$O34)>0)*(COUNTIF($G34:$O$59,$G34:$O34)=1)*(COUNTIF($B$33:$B33,$G34:$O34)=0))>0,LARGE(IF((LEN($G34:$O34)>0)*(COUNTIF($G34:$O$59,$G34:$O34)=1)*(COUNTIF($B$33:$B33,$G34:$O34)=0),COLUMN($G34:$O34)-COLUMN($G34)+1),1),INDEX(SMALL(IF(LEN($G34:$O34)>0,COLUMN($G34:$O34)-COLUMN($G34)+1),{1,2,3,4,5,6,7,8,9}),1,RANDBETWEEN(1,SUMPRODUCT(--ISNUMBER(SMALL(IF(LEN($G34:$O34)>0,COLUMN($G34:$O34)-COLUMN($G34)+1),{1,2,3,4,5,6,7,8,9})))))))

In short, if one of the options has not been chosen yet and this is his/her last available day, then he/she is assigned. Otherwise, a random selection from the available candidates will be made. Take a look at the attachment to see if it's working as desired: