Hello all, I have been trying to come up with a solution that will allow me to NOT use RAND() for a basic scheduling tool.
1) We have 10 people
2) There are 5 shifts (Monday-Friday)
3) There is an OMIT field, marked as 0 or 1, where if omit=1, they are not scheduled for Monday or Tuesday.
This part I've figured out ok, using
=IF(AND(E2=0,ISNUMBER(SEARCH($F$1,C2))),A2,)
column A = the name of the employee.
We have blank entries on the days they are not working.
So as a sample, you would have something like
|Name|Days Volunteered|Omit|Count of Days Available|Monday|Tuesday|Wednesday|Thursday|Friday|
|Joe|Monday, Tuesday, Thursday|0|3|Joe|Joe| |Joe| |
|Sally|Monday, Tuesday, Wednesday,Friday|1|2| | |Sally|Sally|
With a large number of people, I will have some who can work Mondays and some who cant. I'd like to select from the column of people who can work Mondays, but I really hate how RAND() regenerates everytime I hit return, it totally messes things up and defeats the purpose of a schedule -- that it be scheduled and distributed to people -- so I can't have it changing all the time. So what are my options, if I want the tool to select from all the nonblank entries in a column at random?
I think I would use RANDBETWEEN...
but ==INDEX(H2:H10,SMALL(IF($H$2:$H$10<>"",ROW($H$2:$H$10)-ROW($H$2)+1),RANDBETWEEN(1,6))) returns an error. (where H2:H10 is the entries of people who can work Monday)
Thank you all!
Rachelle
1) We have 10 people
2) There are 5 shifts (Monday-Friday)
3) There is an OMIT field, marked as 0 or 1, where if omit=1, they are not scheduled for Monday or Tuesday.
This part I've figured out ok, using
=IF(AND(E2=0,ISNUMBER(SEARCH($F$1,C2))),A2,)
column A = the name of the employee.
We have blank entries on the days they are not working.
So as a sample, you would have something like
|Name|Days Volunteered|Omit|Count of Days Available|Monday|Tuesday|Wednesday|Thursday|Friday|
|Joe|Monday, Tuesday, Thursday|0|3|Joe|Joe| |Joe| |
|Sally|Monday, Tuesday, Wednesday,Friday|1|2| | |Sally|Sally|
With a large number of people, I will have some who can work Mondays and some who cant. I'd like to select from the column of people who can work Mondays, but I really hate how RAND() regenerates everytime I hit return, it totally messes things up and defeats the purpose of a schedule -- that it be scheduled and distributed to people -- so I can't have it changing all the time. So what are my options, if I want the tool to select from all the nonblank entries in a column at random?
I think I would use RANDBETWEEN...
but ==INDEX(H2:H10,SMALL(IF($H$2:$H$10<>"",ROW($H$2:$H$10)-ROW($H$2)+1),RANDBETWEEN(1,6))) returns an error. (where H2:H10 is the entries of people who can work Monday)
Thank you all!
Rachelle
