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" wrote in message
...
Clever, Dana!
--
Jim
"Dana DeLouis" wrote in message
...
| 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"
| wrote in message
| news:dataheadache.1wbxid_1128348311.3967@excelforu m-nospam.com...
|
| 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
|
|
|