Work Rota - Do I need a formula?

**dataheadache** · 10-02-2005, 02:29 PM

Hi everyone.

I'm new around here, so forgive me if this or something similar has been asked before (I would have done a search, but wasn't sure what to search on!)

Please have a read of what I am trying to accomplish, and if you can help - please do, if I'm in totally the worng area to ask this, please point me in the correct direction.

This is what I am trying to do:

I have been asked by the powers that be to create a new work rota - the work rota is made up from 15 people (numbered 1 - 15 for simplicity) and the rota has to be made up a specific way, as there are 15 people, there are 15 different work patterns (we call them cycles 1 - 15 with 15 days in each cycle) - now this work rota has to be laid out in such a fashion so that there employee numbers don't follow on i.e. (1, 2, 3 etc.) and also so that there is no patterm to the work rota (so a cycle would be 1 followed by 4 then 7 and 14 then 2 etc) and no two work rotas are the same - for example:

(emp = employee number)

Please Login or Register  to view this content.

I know this sounds very complicated and I think it can be

- but what I want to know and I have little experience with Excel is can I do this in excel and is there some sort of formula to be able to work it out or can this sort of thing only be done manually?

If there is a formula or if anybody has a solution on how to do this, I would be extremely grateful to them to help me solve this little problem.

I hope I have explained what I am trying to do well enough for anybody else to understand, if not, let me know and I'll try and explain it a little better.

Thanks to anybody in advance who can help!

Jim Rech · 10-02-2005, 07:05 PM

I'm not familiar with the term "rota". Is it a Britishism? From the
context it seems to mean "work schedule" and yet it seems to be randomly
generated (or that's how I read your message). That seems a bit odd to me
in that it doesn't take into account people's availability. So is that
really what you want - 15 unique combinations of the numbers 1 to 15? Excel
can produce random numbers (pseodo-random anyway).

--
Jim
"dataheadache" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi everyone.
>
> I'm new around here, so forgive me if this or something similar has
> been asked before (I would have done a search, but wasn't sure what to
> search on!)
>
> Please have a read of what I am trying to accomplish, and if you can
> help - please do, if I'm in totally the worng area to ask this, please
> point me in the correct direction.
>
> This is what I am trying to do:
>
> I have been asked by the powers that be to create a new work rota - the
> work rota is made up from 15 people (numbered 1 - 15 for simplicity) and
> the rota has to be made up a specific way, as there are 15 people, there
> are 15 different work patterns (we call them cycles 1 - 15 with 15 days
> in each cycle) - now this work rota has to be laid out in such a
> fashion so that there employee numbers don't follow on i.e. (1, 2, 3
> etc.) and also so that there is no patterm to the work rota (so a cycle
> would be 1 followed by 4 then 7 and 14 then 2 etc) and no two work rotas
> are the same - for example:
>
> (emp = employee number)
>
> Code:
> --------------------
>
> Cycle 1 Cycle 2
>
> Day 1 emp 1 emp 4
> Day 2 emp 9 emp 12
> Day 3 emp 5 emp 3
> --------------------
>
>
>
> I know this sounds very complicated and I think it can be

- but
> what I want to know and I have little experience with Excel is can I do
> this in excel and is there some sort of formula to be able to work it
> out or can this sort of thing only be done manually?
>
> If there is a formula or if anybody has a solution on how to do this, I
> would be extremely grateful to them to help me solve this little
> problem.
>
> I hope I have explained what I am trying to do well enough for anybody
> else to understand, if not, let me know and I'll try and explain it a
> little better.
>
> Thanks to anybody in advance who can help!
>
>
> --
> dataheadache
> ------------------------------------------------------------------------
> dataheadache's Profile:
> http://www.excelforum.com/member.php...o&userid=27734
> View this thread: http://www.excelforum.com/showthread...hreadid=472444
>

**dataheadache** · 10-03-2005, 04:28 AM

This particular schedule (or rota) does not need to take in people's availablility, and yes, the randomness is what I need, but how can you get Excel to pick 15 numbers and put them randomly on every line of a 15x15 grid, so that no number (sequentially) is next to another?

Jim Rech · 10-03-2005, 09:05 AM

>>how can you get Excel to pick 15 numbers and put them randomly on every
>>line of a
15x15 grid, so that no number (sequentially) is next to another?

I don't know. There is no way to do this by formula but it could be done by
macro, except for the "no number (sequentially) is next to another"
requirement. You see, the macro could randomly pick the first number and
then a second (excluding the first and the next number after it) but I don't
see how you can be sure at some point you do not have only sequential
numbers to pick from like 3,4 and 5.

--
Jim
"dataheadache" <[email protected]>
wrote in message
news:[email protected]...
|
| This particular schedule (or rota) does not need to take in people's
| availablility, and yes, the randomness is what I need, but how can you
| get Excel to pick 15 numbers and put them randomly on every line of a
| 15x15 grid, so that no number (sequentially) is next to another?
|
|
| --
| dataheadache
| ------------------------------------------------------------------------
| dataheadache's Profile:
http://www.excelforum.com/member.php...o&userid=27734
| View this thread: http://www.excelforum.com/showthread...hreadid=472444
|

**dataheadache** · 10-03-2005, 09:25 AM

Thank you, Jim for your valuble input, but I have now done it by using pencil and paper alone. It took about 60 attempts and working out a sort of pattern to follow to ensure that no sequential number was next to it - but in the end it worked.

Thanks again.

Dana DeLouis · 10-03-2005, 11:05 AM

Hi. If you would like a possible macro that Jim mentioned, here is one of a
few ways.
Note that there are =FACT(15), or 1,307,674,368,000 possible permutations
of 15.
I note that 12,13 is not good, but 13,12 is good because it's not an
increasing sequence.
This uses a helper column to check if two adjacent numbers are sequential,
and randomly sorts the numbers 1-15. It took less than 1 second.
This doesn't technically insure there are no duplicates, but the odds are
low. You could adjust the output from 15 to say 20 if you wish.

Sub Demo()
'// Dana DeLouis
Dim R As Long

[C1] = 1
[C2] = 2
[C1:C2].AutoFill Destination:=Range("C1:C15"), Type:=xlFillDefault

[D1].Formula = "=RAND()"
[D1].AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault

[A1].FormulaR1C1 = "=--(RC[2]+1=R[1]C[2])"
[A1].AutoFill Destination:=Range("A1:A14"), Type:=xlFillDefault

[A16].FormulaR1C1 = "=SUM(R[-15]C:R[-2]C )"

For R = 1 To 15
Do While [A16] > 0
[C1:D15].Sort Key1:=Range("D1")
Loop
[C1:C15].Copy
Cells(R, 6).PasteSpecial Transpose:=True
[C1:D15].Sort Key1:=Range("D1")
Next R
End Sub

--
Dana DeLouis
Win XP & Office 2003

"dataheadache" <[email protected]>
wrote in message
news:[email protected]...
>
> Thank you, Jim for your valuble input, but I have now done it by using
> pencil and paper alone. It took about 60 attempts and working out a
> sort of pattern to follow to ensure that no sequential number was next
> to it - but in the end it worked.
>
>
> Thanks again.

>
>
> --
> dataheadache
> ------------------------------------------------------------------------
> dataheadache's Profile:
> http://www.excelforum.com/member.php...o&userid=27734
> View this thread: http://www.excelforum.com/showthread...hreadid=472444
>

Jim Rech · 10-03-2005, 02:05 PM

| >
| >
| > --
| > dataheadache
| > ------------------------------------------------------------------------
| > dataheadache's Profile:
| > http://www.excelforum.com/member.php...o&userid=27734
| > View this thread:
http://www.excelforum.com/showthread...hreadid=472444
| >
|
|

Dana DeLouis · 10-03-2005, 04:05 PM

Hi. I was just curious on the number of solutions from such a large number
of permutations.
Looks like an exact equation exists which uses the incomplete Gamma function
(from z=-1).
However, we can get an excellent approximation to this with the following...

=ROUND(FACT(n+1)/(EXP(1)*(n)),0)

With n=15, there are 513,137,616,783 possible solutions, which I believe is
correct.
This is just the number of solutions in which no two numbers are in
ascending sequential order.
Again, ... just curious. ;>)

--
Dana DeLouis
Win XP & Office 2003

"Jim Rech" <[email protected]> wrote in message
news:[email protected]...
> Clever, Dana!
>
> --
> Jim
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
> | Hi. If you would like a possible macro that Jim mentioned, here is one
> of
> a
> | few ways.
> | Note that there are =FACT(15), or 1,307,674,368,000 possible
> permutations
> | of 15.
> | I note that 12,13 is not good, but 13,12 is good because it's not an
> | increasing sequence.
> | This uses a helper column to check if two adjacent numbers are
> sequential,
> | and randomly sorts the numbers 1-15. It took less than 1 second.
> | This doesn't technically insure there are no duplicates, but the odds
> are
> | low. You could adjust the output from 15 to say 20 if you wish.
> |
> | Sub Demo()
> | '// Dana DeLouis
> | Dim R As Long
> |
> | [C1] = 1
> | [C2] = 2
> | [C1:C2].AutoFill Destination:=Range("C1:C15"), Type:=xlFillDefault
> |
> | [D1].Formula = "=RAND()"
> | [D1].AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
> |
> | [A1].FormulaR1C1 = "=--(RC[2]+1=R[1]C[2])"
> | [A1].AutoFill Destination:=Range("A1:A14"), Type:=xlFillDefault
> |
> | [A16].FormulaR1C1 = "=SUM(R[-15]C:R[-2]C )"
> |
> | For R = 1 To 15
> | Do While [A16] > 0
> | [C1:D15].Sort Key1:=Range("D1")
> | Loop
> | [C1:C15].Copy
> | Cells(R, 6).PasteSpecial Transpose:=True
> | [C1:D15].Sort Key1:=Range("D1")
> | Next R
> | End Sub
> |
> |
> | --
> | Dana DeLouis
> | Win XP & Office 2003
> |
> |
> | "dataheadache"
> <[email protected]>
> | wrote in message
> | news:[email protected]...
> | >
> | > Thank you, Jim for your valuble input, but I have now done it by using
> | > pencil and paper alone. It took about 60 attempts and working out a
> | > sort of pattern to follow to ensure that no sequential number was next
> | > to it - but in the end it worked.
> | >
> | >
> | > Thanks again.

> | >
> | >
> | > --
> | > dataheadache
> |
> > ------------------------------------------------------------------------
> | > dataheadache's Profile:
> | > http://www.excelforum.com/member.php...o&userid=27734
> | > View this thread:
> http://www.excelforum.com/showthread...hreadid=472444
> | >
> |
> |
>
>

**dataheadache** · 10-03-2005, 04:08 PM

Originally Posted by Dana DeLouis

I note that 12,13 is not good, but 13,12 is good because it's not an
increasing sequence.

Thanks Dana, but by sequentially, I meant up and down of a number
so

1 2 3 won't work
3 2 1 won't work
1 3 4 2 7 etc. will work.

Any possibility the macro could be further extended to stop it allowing sequential numbers downward as well as upward as originally intended?

Jim Rech · 10-03-2005, 05:05 PM

>>there are 513,137,616,783 possible solutions

Looks like the employees will never be bored!

--
Jim
"Dana DeLouis" <[email protected]> wrote in message
news:%[email protected]...
| Hi. I was just curious on the number of solutions from such a large
number
| of permutations.
| Looks like an exact equation exists which uses the incomplete Gamma
function
| (from z=-1).
| However, we can get an excellent approximation to this with the
following...
|
| =ROUND(FACT(n+1)/(EXP(1)*(n)),0)
|
| With n=15, there are 513,137,616,783 possible solutions, which I believe
is
| correct.
| This is just the number of solutions in which no two numbers are in
| ascending sequential order.
| Again, ... just curious. ;>)
|
| --
| Dana DeLouis
| Win XP & Office 2003
|
|
| "Jim Rech" <[email protected]> wrote in message
| news:[email protected]...
| > Clever, Dana!
| >
| > --
| > Jim
| > "Dana DeLouis" <[email protected]> wrote in message
| > news:[email protected]...
| > | Hi. If you would like a possible macro that Jim mentioned, here is
one
| > of
| > a
| > | few ways.
| > | Note that there are =FACT(15), or 1,307,674,368,000 possible
| > permutations
| > | of 15.
| > | I note that 12,13 is not good, but 13,12 is good because it's not an
| > | increasing sequence.
| > | This uses a helper column to check if two adjacent numbers are
| > sequential,
| > | and randomly sorts the numbers 1-15. It took less than 1 second.
| > | This doesn't technically insure there are no duplicates, but the odds
| > are
| > | low. You could adjust the output from 15 to say 20 if you wish.
| > |
| > | Sub Demo()
| > | '// Dana DeLouis
| > | Dim R As Long
| > |
| > | [C1] = 1
| > | [C2] = 2
| > | [C1:C2].AutoFill Destination:=Range("C1:C15"), Type:=xlFillDefault
| > |
| > | [D1].Formula = "=RAND()"
| > | [D1].AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault
| > |
| > | [A1].FormulaR1C1 = "=--(RC[2]+1=R[1]C[2])"
| > | [A1].AutoFill Destination:=Range("A1:A14"), Type:=xlFillDefault
| > |
| > | [A16].FormulaR1C1 = "=SUM(R[-15]C:R[-2]C )"
| > |
| > | For R = 1 To 15
| > | Do While [A16] > 0
| > | [C1:D15].Sort Key1:=Range("D1")
| > | Loop
| > | [C1:C15].Copy
| > | Cells(R, 6).PasteSpecial Transpose:=True
| > | [C1:D15].Sort Key1:=Range("D1")
| > | Next R
| > | End Sub
| > |
| > |
| > | --
| > | Dana DeLouis
| > | Win XP & Office 2003
| > |
| > |
| > | "dataheadache"
| > <[email protected]>
| > | wrote in message
| > | news:[email protected]...
| > | >
| > | > Thank you, Jim for your valuble input, but I have now done it by
using
| > | > pencil and paper alone. It took about 60 attempts and working out a
| > | > sort of pattern to follow to ensure that no sequential number was
next
| > | > to it - but in the end it worked.
| > | >
| > | >
| > | > Thanks again.

| > | >
| > | >
| > | > --
| > | > dataheadache
| > |
| >
> ------------------------------------------------------------------------
| > | > dataheadache's Profile:
| > | > http://www.excelforum.com/member.php...o&userid=27734
| > | > View this thread:
| > http://www.excelforum.com/showthread...hreadid=472444
| > | >
| > |
| > |
| >
| >
|
|

Dana DeLouis · 10-03-2005, 06:05 PM

> 1 3 4 2 7 etc. will work.

Hi. I guess I'm a little confused because I thought a pattern with ...3,
4... were not allowed because these two numbers are "next" to each other in
ascending order.

--
Dana DeLouis
Win XP & Office 2003

"dataheadache" <[email protected]>
wrote in message
news:[email protected]...
>
> Dana DeLouis Wrote:
>>
>> I note that 12,13 is not good, but 13,12 is good because it's not an
>> increasing sequence.
>
> Thanks Dana, but by sequentially, I meant up and down of a number
> so
>
> 1 2 3 won't work
> 3 2 1 won't work
> 1 3 4 2 7 etc. will work.
>
> Any possibility the macro could be further extended to stop it allowing
> sequential numbers downward as well as upward as originally intended?
>
>
> --
> dataheadache

Sandy Mann · 10-03-2005, 06:05 PM

Dataheadache,

I have also been assuming, (and this is an additional reason why I could
never suggest an answer to your problem), that each row should have the
numbers 1-15 without dulplicates, (obviously), but also each column of 15
rows should also have the numbers 1 - 15 without duplicates. In other words

> are 15 different work patterns (we call them cycles 1 - 15 with 15 days
> in each cycle)

means that no employee should be scheduled to the same task again until all
other 14 staff have also been scheduled to it.

This would make the rota like a large 15 by 15 Sudoku puzzel with no number
repeated in a row or column.

There was some talk in the NG's the other day of Suduko solving spreadsheet,
I wonder if that spreadsheet, (which I haven't even looked at), could be
expanded to solve your problem.

Just a thought.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

"dataheadache" <[email protected]>
wrote in message
news:[email protected]...
>
> Dana DeLouis Wrote:
>>
>> I note that 12,13 is not good, but 13,12 is good because it's not an
>> increasing sequence.
>
> Thanks Dana, but by sequentially, I meant up and down of a number
> so
>
> 1 2 3 won't work
> 3 2 1 won't work
> 1 3 4 2 7 etc. will work.
>
> Any possibility the macro could be further extended to stop it allowing
> sequential numbers downward as well as upward as originally intended?
>
>
> --
> dataheadache
> ------------------------------------------------------------------------
> dataheadache's Profile:
> http://www.excelforum.com/member.php...o&userid=27734
> View this thread: http://www.excelforum.com/showthread...hreadid=472444
>