# equally distribute a range of numbers subject to constraints

#### justinp2

##### New Member
Hello.

I am trying to use excel to schedule on-call employees.

There are 7 employees, each assigned an ID# from 1-7. I then populate a sequential list of dates out from today to 3 months in the future (for arguments sake cells A1:A90).

In cell B1:B90 should be the employee assignments. However, i have to contend with booked vacations and I don't want people to be on call within two days of their last on call. Thus, in columns C:G i put in who has requested the day off (if employee 1 and 2 want the day off i would enter 1 in C1 and 2 in C2).

I was sort of able to make this work (with help from the forum) by using this function:

Code:
Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long
Dim C As Range
Dim JoinRange As Range

Set JoinRange = Union(Exclude1, Exclude2)

Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In JoinRange
If R = C Then Exit For
Next C
Loop Until C Is Nothing

RandBetweenInt = R
Application.Volatile
End Function

What that allows me to do is for each row in column B, randomly assign a number between 1 and 7 (or any range i pick), however exclude from that range the the two rows in B above the cell of interest (the last two people who were on call), and also exclude the range to the right of column B (vacation requests). It works (and if i could post attachments i would upload the spreadsheet).

However, the issue is that it is "random". Ideally, i would like to as equitably as possibly distribute the work assignments subject to the constraints so that when i count how many times each employee is on call for the period they are more or less equitably distributed. Through the 90 cells i would like to see as equally as possible the same number of 1,2,3,4,5,6,7... A randomized distribution would probably work fairly well if i scheduled a year out, but on the 3 month window there is too much variation.

I don't know if excel is the animal for this or not. Does anybody have any suggestions?

thank you all.
Justin

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Replies
1
Views
197
Replies
2
Views
230
Replies
0
Views
594
Replies
10
Views
446
Replies
3
Views
133

1,196,116
Messages
6,013,563
Members
441,771
Latest member
clamnets

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back