Search formule to calculate combinations

  • Thread starter Thread starter Jacko's Mailbox
  • Start date Start date
J

Jacko's Mailbox

How can i calculate my own fix numbers combinations ? :

- 5 Fix possible numbers for each of 6-cells to use,
- Seperated in 6 colums
- Each colum out of 6 has his own 5-different numbers.

Now i want to see al the combinations there are
with those numbers , and numbers may not change from 1 cell to another.

ex.
column A has 1-2-3-4-5 only for cell 1
column B has 6-7-8-9-10 only for cell 2
column C has 11-12-13-14-15 only for cell 3
etc..

For 6 cells each his own column of 5 combinations
but need all possible combination viewed in entire 6 cells row..

like 1 , 6 ,11 ,.. ,.., .. as 1 combination

Greetings
Jacko
 
Jacko [ a.k.a The Wraith? ]
- It's a good idea to stick to the same thread in the ng

Here's the reply I've just posted to "The Wraith" in microsoft.public.excel
--------------------
Try this revised set-up?

In Sheet1
-----------
Put in A1: =RAND()
Copy A1 across to F1, then copy down to F5

Create the following named ranges
[ via Insert > Name > Define ]:

MyT1 =Sheet1!$A$1:$A$5
MyT2 =Sheet1!$B$1:$B$5
MyT3 =Sheet1!$C$1:$C$5
MyT4 =Sheet1!$D$1:$D$5
MyT5 =Sheet1!$E$1:$E$5
MyT6 =Sheet1!$F$1:$F$5

Select A7:A11

Put in the formula bar:

=RANK(INDIRECT("MyT"&COLUMN()),INDIRECT("MyT"&COLUMN()))

Array-enter the formula as described earlier
[ i.e. press CTRL + SHIFT + ENTER ]

With A7:A11 selected, copy the array formula across to F7:F11

Put in:

A13: =CHOOSE(A7,1,2,3,4,5)
B13: =CHOOSE(B7,6,7,8,9,10)
C13: =CHOOSE(C7,11,12,13,14,15)
D13: =CHOOSE(D7,16,17,18,19,20)
E13: =CHOOSE(E7,21,22,23,24,25)
F13: =CHOOSE(F7,26,27,28,29,30)

Select A13:F13, copy down to A17:F17

In A13:F17 will be 5 random sets [i.e rows] of 6 numbers each
where:

A13:A17 = random pick only from 1,2,3,4,5
B13:B17 = random pick only from 6,7,8,9,10
C13:C17 = random pick only from 11,12,13,14,15
D13:D17 = random pick only from 16,17,18,19,20
E13:E17 = random pick only from 21,22,23,24,25
F13:F17 = random pick only from 26,27,28,29,30

Each press of F9 key will re-generate another 5 random sets of 6 numbers
 
Back
Top