Formula to calculate combinations

  • Thread starter Thread starter The Wraith
  • Start date Start date
T

The Wraith

Hi..

I'm in a experimental phase about random numbers..
Need help again..
First thx to all who allready helped me out with early problems..


--1-- I have 1 row (6 cells)
--2-- Each cell has 5 possible numbers (in seperated cells)
--3-- I need x-times 6 numbers a row (out of list 5 numbers)
--4-- Non of a row may have same numbers in it


---How can i calculate all the combinations (numbers are betwee
(1-42) each cell has 5 randoms out this range putted in seperate
cells.

---How to put all those combinations in a nice list ?

Thx to the specialists.
 
Just hazarding a guess that this kind of set-up
might be quite near? to what you're seeking

Put in A1: =RAND()
Copy A1 across to F1, then copy down to F7

Name the range A1:F7 as : Table1

Now select a 6 col x 7 row grid, say select A9:F15

Put in the *formula bar*: =RANK(Table1,Table1)

*Array-enter* the formula, that is:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly Excel will wrap curly braces {} around the formula, i.e.:
{=RANK(Table1,Table1)}

Do *not* type-in the curly braces yourself

In A9:F15 will be a random scramble of all 42 numbers [1-42]
with 6 different numbers per row, in 7 rows altogether
i.e 7 unique sets of 6 numbers each

Press F9 key to generate another
random scramble of all 42 numbers in A9:F15

---

To calculate the total number of unique sets of 6 numbers each
which can be chosen from a field of 42 numbers, use COMBIN()

Put in any cell: =COMBIN(42,6)
which returns 5,245,786
 
Hi

Ok the formulas are working,
I tested them out , thx..
But,....
This is still a combination out of the random (42,6) gives me 5.245.78
combination in 6 cells sets..

But 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 t
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 to the specialists
 
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