Fair Rota distribution (excel 07)

**junk87** · 05-09-2013, 02:57 PM

Hello,

I am trying to improve a rota system in my workplace with the use of excel. I have 30 people who work 5 days each a week. Some work days, some evenings and not everybody has the same days off (ie. a 3rd work sundays).

During their shifts, at least 3 people are required at one time to do a particular task. Currently, we have 3 people doing it in the day and then 3 people take over in the evenings. Everybody on the team must also be involved in this task, so it must be shared fairly.

At this time, the rota is determined by looking at the shift rota and selecting who does what when.

However, I would like this to be done automatically in excel. I have inputted the shifts into a spreadsheet and used the formula =INDEX($C$3:$C$14,RANDBETWEEN(1,COUNTA($C$3:$C$14)),1) to randomly select who is scheduled to do the task, but this is not as fair as I would like it to be.

Over a length of time, this doesn't select people at random, ensuring that everybody has had the same number of opportunities to get involved.

Is this possible to achieve in excel and if so, how can I do it? Alternative methods of achieving this task are welcome.

Thank you .

**AndyLitch** · 05-09-2013, 03:51 PM

What you need is a form or realistic "fairness" analysis which is easy to mathematically model ... That is to say every individual is tasked over the course of a working year on an even basis ...
To start the process - we probably need a sample workbook uploading.

**junk87** · 05-09-2013, 05:40 PM

I can't upload one at the moment, sorry. Do you have any examples in the meantime?

**junk87** · 05-10-2013, 07:07 PM

Any ideas?

I was thinking of maybe using a macro to copy the results into a list and the maybe use vlookups or hlookups to maybe assist.

Maybe, or maybe not?

**junk87** · 05-22-2013, 10:56 AM

This has been resolved.

Please let me know if you would like an attached example.

Thank you

**AndyLitch** · 05-22-2013, 12:57 PM

I've been offline the last couple of days - sorry about that... If you've resolved the problem that's great.. No need to upload but you can use it to help someone else

**p911pew** · 06-12-2013, 03:18 PM

I would love an upload if you can, I am looking to distribute a phone rota system in much the same way, I have some staff that work less hours so it would be unfair to ask them to do the same amount of phone cover than those who are there the full working week.

Cheers
Tom

**junk87** · 07-27-2013, 01:28 PM

Originally Posted by p911pew

I would love an upload if you can, I am looking to distribute a phone rota system in much the same way, I have some staff that work less hours so it would be unfair to ask them to do the same amount of phone cover than those who are there the full working week.

Cheers
Tom

Sorry, but I have been away. Do you still need an upload to help?