How to randomise while being fair (small dataset)

[rory1111]

I have a small dataset approx 60. (lets just say they are work tasks)

I would like to randomly assign tasks to people, but i would also like assign an equal amount of tasks where possible.


so if i were to have 63 tasks

and 6 people (adam, ben, colm, derek, elvis, fiona)

I would expect/hope to assign tasks (adam 10, ben 11, colm 10, derek 10, elvis 11, fiona 11)


my efforts so far will random assign, however results are not fair because of small dataset.


results simular to thes are returned 4,7,15,10,11,13 (some people have 3 times as much work as others)


In (A) I use =RANDBETWEEN(1,6) and across the sheet I assigna number between 1 and 6 to all 6 people, Vlookup does the rest


Does anyone have any ideas on how to distribute tasks in a fair way.

 

[v4wade]

Sorry to say this, but I am not getting a clear idea about yor problem frm what u hv described in above post. can u please share 'that' excel sheet (with some temporary data) & pls describe yor problem in detail ? My mail ID is 'wade2586@gmail.com'

 

[NARAYANK991]

Hi Rory ,


I think it's impossible to make a random selection absolutely fair ; it would not be random in that case.


What you can do is :


1. Divide the number of tasks by the number of people , and take the integer value ; in your specific example , this would be 10.


2. Now , the way to divide the tasks fairly would be to use a formula such as the following :


=IF(ROW()-ROW(List_of_people)<Number_of_Tasks-Base_Tasks_per_person*Number_of_people,Base_Tasks_per_person+1,Base_Tasks_per_person)


This ensures that the people at the top of the list get the higher number of tasks , and the people at the lower end of the list get the base number of tasks.


In your case , since INT(63/6) is 10 , the three people at the top of the list would each get 10 + 1 i.e. 11 tasks , while the three people at the lower end of the list would each get 10.


In the above formula :


List_of_people is the range having the names of the people.


Number_of_Tasks is the cell having the number 63.


Number_of_people is a calculated named range referring to : =ROWS(List_of_people)


Base_Tasks_per_person is a calculated named range referring to : =INT(Number_of_Tasks/Number_of_people)


Narayan

 

[rory1111]

Hello all,


Thank you for your interest/help.


@ Narayan,Thank you for your suggestion/input. After reviewing you suggestion and then implementing it into some test data i got it to work, however the result is basically the number of tasks/number of people. I am not really sure how that can be used assign workload.


I was thinking of continuously refreshing the sheet until all three people have a number that is within 2/3 of the average or do you's have any better ideas.


I have added my test sheet here and made some adjustments see f2,f3,f4,f5


any ideas on how to have the sheet refreshed until f5 = 3, or now that you can see a test sheet do you have a better solution


https://docs.google.com/file/d/0B23b7VmIjh_VdHJZd0VyeFBnU28/edit

 

[NARAYANK991]

Hi Rory ,


I am sorry but I cannot understand what your requirement is.


As far as I can see , you have 3 people amongst whom you would like to distribute 253 tasks ; if you put in the formula I posted earlier , you would get 85 tasks for the first person , and 84 each for person 2 and person 3.


Now , if you want to distribute the tasks themselves amongst these 3 people , in a random fashion , then that is a different matter ; that can be done simply.


Can you specify exactly what you want to do ?


Narayan

 

[rory1111]

Hi Narayan,


I'm sorry if i'm unclear. I would like each task to be assigned to one of the people, at the end i want and equal amount or very close to equal to be assigned to each person.

In my example (above link) it will randomly assign a person to a task (press f9) to see what i mean. the problem is that because the data is so small the assigned cases are not very fair.

example is below


73 tasks for jim

86 tasks for paul

94 tasks for dean


if i press f9 again

i get

91 tasks for jim

79 tasks for paul

93 tasks for dean


realistically i would like the amount of tasks each person to get to be as close to the base_tasks_per_month, cases must also be random


so excel should say

we have 253 tasks to be divided between jim, paul and dean. so jim will have 85, paul 84 and dean 84.

now of the 253 tasks i will need to randomly assign jim to 85, paul to 84 and dean to 84.


hope this clears it up

 

[NARAYANK991]

Hi Rory ,


What I had posted initially would do the same thing !


Can you download the file from here and see if it is what you want ?


https://docs.google.com/file/d/0B0KMpuzr3MTVOVFfUFlLVzhoUXc/edit


There are two items of data :


1. The list of people , in your case this is in the range I2:I4


2. The total number of tasks , in your case this is in cell L1.


You need to incorporate these in the defined named ranges.


Everything else is using formulae.


Narayan