Ignore Blanks and Choose Between Candidates

techbelle

New Member
Joined
Nov 21, 2017
Messages
2
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

 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top