I'm trying to figure out how to generate all combinations from a list
of N values in a column, let's say column A.
In particular, I want to have all combinations of 2 values, 3 values,
4, ... up to 7 values.
To give an example: Let's assume I have a list of only 3 values
(1,2,3) for which I want to have all combinations of two values. In
this case, the result would be 1,2; 1,3; and 2;3. The ordering of the
values does not matter, i.e. duplicates should be eliminated.
I hope I could explain it sufficiently. I would be grateful for any
help.
Thanks,
Andreas
For your example, to select 2 object from a list of three use =Combin(3,2).
And just for interest's sake, if order did matter you should use the
Permut() function.
Hope this is what you need.
"Andreas" <agr...@web.de> wrote in message
news:62fef7e5-b040-4166...@a22g2000hsc.googlegroups.com...
Take away this implemented sample from my archives:
http://www.savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls
(full details inside, ready to run)
In the sample file,
In Sheet1,
1. Enter the letter C or P in A1
(C = combinations, P = permutations), eg enter: C
2. Enter the number of items involved per combo in A2, eg enter: 2
(this fig would be your: 2 values, 3 values, 4, ... up to 7 values.)
3. Enter/List the N items in A3 down (your "N values in a column")
4. Select A1 (this cell selection is required),
then click the button ListPermutations to run the sub ListPermutations
5. The results will be written to a new sheet (just to the left),
and wrap in a zig-zag manner until all combos are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003
--------
Repeat the runs for each of your desired values (Step 2 above): 3,4,5 ... 7
Go easy when you "ramp up" the generation
(increasing picks on increasing N values)
As a sanity check, for example:
a "Pick 6 out of 45" run will work out to a staggering:
=COMBIN(45,6) = 8,145,060 combinations
so almost half** an entire sheet would be populated
(You sure you want to do this ??)
**A single sheet in xl97 to xl2003 houses:
=65536 rows x 256 cols = 16,777,216 cells
The sub would certainly need time to complete generation
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Andreas" <agr...@web.de> wrote in message
news:62fef7e5-b040-4166...@a22g2000hsc.googlegroups.com...
Thanks, this is exactly what I need. However, my largest problem is 7
out of 66 = 778,789,440 combinations. Is there a possibility to do
this in Excel? Or save the data in a txt file and then read it by
another application? What I need later is the median and mean of each
of the combinations.
Thanks,
Andreas
Andreas
Thanks, Max
For SPSS, I assume you will generate all 778,789,440 subsets, and then
"Count" how many of each "mean" you have. (I'll skip Median for now).
I'll just throw this out for consideration. Your "Mean" problem could be
simplified with a Generating Function:
I won't list the vba code, but here is a math program to explain one way to
do this very quickly.
Here is the generating function for your means: (y is your subset size of 7
later)
gf = Product[1 + x^k y, {k, 66}];
Hence, we can calculate "All" Means right away from the series...
Means = CoefficientList[Coefficient[gf, y^7], x] // Rest;
(I dropped the zero indexed term)
What I mean here by Mean is the sum of your 7 numbers in each subset prior
to dividing by 7.
The smallest subset is {1,2,3,4,5,6,7} whose total is 28.
There is only one subset that totals 28
Means[[28]]
1
or in other words, has a mean of 4.
28/7.
4.
We can immediately tell that the largest count is a total of 234 (& 235)
Means[[234]]
6,327,599
234/7.
33.4286
In other words, we can immediately see that there are 6,327,599 subsets of
size 7 that have a mean of 33.4286 from a set of 66.
As a quick check, if I total all the solutions...
Means // Total
778,789,440
It matches the expected sum.
Binomial[66, 7]
778,789,440
Which checks with Excel:
=COMBIN(66,7)
778,789,440
- -
HTH :>)
Dana DeLouis
<graefe....@gmail.com> wrote in message
news:dcf2d950-4e61-437a...@27g2000hsf.googlegroups.com...