Filling a rota fairly

[Brian Clarke]

There are 5 staff members who want to park their cars at the office, but
only 3 parking spaces. So the spaces need to be allocated by rotation.

I looked at the past rotas, and they don't seem to have been fair. It
would be good to do the allocation automatically, perhaps in Excel. If
we use cols D-F for the parking spaces and a row for each week, is there
a general method which will quickly fill in the rota?

I know that this is a relatively trivial task to do manually, but it's a
type of problem I haven't come across before, and it's interesting. I
suspect the MOD function might be useful here, but I can't put the thing
together.

 

[Brian Clarke]

Assume the staff members' names are in a row from C2 - G2.

 

[Pete_UK]

You can set up a simple grid like this which covers a 5-day period
(going down) and for 5 cars (going across):

1 2 3 4 5
Y Y Y
Y Y Y
Y Y Y
Y Y Y
Y Y Y

Then just copy this block of 5 days down. Allow staff to swap with one
another within each 5 day period, in case they have a particular need.

Hope this helps.

Pete

 

[GB]

That works very nicely, and there is no need for anything more random. I
would suggest, though, that there are advantages in doing it a week at a
time, rather than daily. For example, the weeks when people cannot park they
can buy a weekly bus pass, holidays tend to be for a week at a time, etc.

 

[Brian Clarke]

That's what we do at the moment. I just wondered if there was some kind
of function or forumla which would do the job.

 

[Pete_UK]

Well, the rows can represent weeks rather than days.

Pete

 

[Paul Hyett]

There are 5 staff members who want to park their cars at the office,
but only 3 parking spaces. So the spaces need to be allocated by
rotation.

I looked at the past rotas, and they don't seem to have been fair. It
would be good to do the allocation automatically, perhaps in Excel. If
we use cols D-F for the parking spaces and a row for each week, is
there a general method which will quickly fill in the rota?

I know that this is a relatively trivial task to do manually, but it's
a type of problem I haven't come across before, and it's interesting. I
suspect the MOD function might be useful here, but I can't put the
thing together.

I know there are 10 combinations of 'any 3 from 5', so a two week cycle
seems reasonable.

 

[Pete_UK]

Pictorially, those 10 combinations are:

Y_Y_Y_n_n
Y_n_Y_Y_n
Y_n_n_Y_Y
Y_Y_n_Y_n
Y_Y_n_n_Y
Y_n_Y_n_Y
n_Y_Y_Y_n
n_Y_Y_n_Y
n_Y_n_Y_Y
n_n_Y_Y_Y

Each car has 6 Y's in a cycle of 10 (days or weeks).

You could allocate a random number to these 10 rows and then sort them
so that you have a random distribution of the allowable combinations.

Hope this helps.

Pete

 

[Julie]

The easiest I can think of is:

Put the dates from A2, down, on the left.
Put the employee names in B2 through B6
Add this formula to each cell below the employees:

=IF(MOD(ROW(E2)+COLUMN(F$2),5)<3,"X","")

Works.

If you care about WHICH parking spot they get, just change the "X" to
MOD(ROW(E2)+COLUMN(F$2),5)+1. That numbers 'em.

If ya wanna get *really* fancy, put =COUNTA(B1:IV1) in cell A1. Then
replace the 5 in the formula with A1...if you hire any more employees,
it'll still work!




Julie

 

[Brian Clarke]

Thanks! I'll try that out. I thought there had to be something involving
the MOD function.

We have done the schedule manually up to now, but it's amazing how many
mistakes can creep in. Some people had ended up with twice as many
spaces over time as some other people.

Brian

 

[Nick]

That's what we do at the moment. I just wondered if there was some kind
of function or forumla which would do the job.

MOD can be used to get the desired result.

if you assume the people are 0,1,2,3,4 AND the day number is held A1

The functions =mod(A1,5), =mod(A1+1,5), =mod(A1+2,5) will allocate the
three spaces.

 

[Julie]

Put the employee names in B2 through B6

Ummm....and I meant A2 to A6, which was probably obvious to you - lol
- but just in case it wasn't. I was tired.

J.

 

[Julie]

Sheesh, and I'm still goofing up. Long week. Formula should read
=IF(MOD(ROW()+COLUMN(),5)<3,"X","")...the way it's listed below will
only work correctly if you start with it in the cell (E2) listed in
the formula...I just grabbed it out of that cell to paste into my
post. No cell addresses are actually necessary.

Sorry!

Julie