Combination with repetitions into excel for multiple data

[Nic91]

Hi guys!

New tothe forum and first post for me, I tried to figure it out on my own and by looking on this forum, but not sure my mind is completely clear.

I have to say I'm quite new to this kind of statistics, so apologize if the question is actually easy!

I want to calculate the total number of possible combinations including repetitions of a set of 5 items, each one of them can assume 3 different values.

so, let's say the items are A,B,C,D and the values are 1,2,3. The combiantion may be 11111, 22222, 33333, 12222, 11222 etc.. I would like to know what is the maximum amount of this combination that can be entered and, if doable, a way to display all this combination in a list.

I tried with =COMBIN(15,3) - being 5*3 the crossing of those combinations and 3 the possible outcomes - and I get 455, which I believe it may be a low number, also, not sure if this includes repetions (ie. 11111) or not. Is it correct?

If so, how can I do to show all the possible combinations in a list?

Thanks a lot to everyone!!

 

[sheetspread]

repetitions of a set of 5 items, each one of them can assume 3 different values.

so, let's say the items are A,B,C,D and the values are 1,2,3. The combiantion may be 11111, 22222, 33333, 12222, 11222 etc.. I would like to know what is the maximum amount of this combination that can be entered and, if doable, a way to display all this combination in a list.

So no zeroes? To find all arrangements:

Excel 2010
	A	B	C	D	E
1	1	1	1	1	1
Sheet2

Cell Formulas
Range	Formula
A1	=MOD(ROUNDUP(ROW(A1)/81,0)-1,3)+1
B1	=MOD(ROUNDUP(ROW(A1)/27,0)-1,3)+1
C1	=MOD(ROUNDUP(ROW(A1)/9,0)-1,3)+1
D1	=MOD(ROUNDUP(ROW(A1)/3,0)-1,3)+1
E1	=MOD(ROW(A1)-1,3)+1

and drag down....(3^5 = 243)

But there will be repeating numbers and repeating groups (disregarding order)

PERMUT gives you repeating numbers but nonrepeating groups (disregarding order)

COMBIN gives you nonrepeating numbers and nonrepeating groups (disregarding order)

 

[Nic91]

So no zeroes? To find all arrangements:

Excel 2010

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=MOD(ROUNDUP(ROW(A1)/81,0)-1,3)+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=MOD(ROUNDUP(ROW(A1)/27,0)-1,3)+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=MOD(ROUNDUP(ROW(A1)/9,0)-1,3)+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=MOD(ROUNDUP(ROW(A1)/3,0)-1,3)+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=MOD(ROW(A1)-1,3)+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



and drag down....(3^5 = 243)

But there will be repeating numbers and repeating groups (disregarding order)

PERMUT gives you repeating numbers but nonrepeating groups (disregarding order)

COMBIN gives you nonrepeating numbers and nonrepeating groups (disregarding order)

thanks a lot Sheetspread - will definitely try the above!

 

[sheetspread]

Cont'd:


you can find all permutations by ordering with a small() formula, then deleting duplicates


you can find all combinations with a FREQUENCY or COUNTIF formula and deleting the ones with repeating 1s,2s,3s....

 

[Nic91]

got it - thanks!

I already tried the answer you gave me above and it works perfectly, I have all the possible combinations as I wanted.

Just a question, what's the logic behind the formulas you sent me? Let's say that tomorrow I want to do the same but the numbers are, for example, 5, 10, 15, 20 - should I change the fina part with -5,15)+5 ?

 

[B___P]

With a little change
in A1 and then copy

=MOD(ROUNDUP(ROW(B3)/(3^(COLUMN()-1)),0)-1,3)+1

Bye

 

[sheetspread]

I have all the possible combinations as I wanted.

Well tuples really

Just a question, what's the logic behind the formulas you sent me? Let's say that tomorrow I want to do the same but the numbers are, for example, 5, 10, 15, 20 - should I change the fina part with -5,15)+5 ?

B__P's method works (changes the base and the power) or you can use shg's popular chinese food menu:

https://app.box.com/s/47b28f19d794b25511be

 

[sheetspread]

Part of post 2 should be restated:

Permutations distinguish between the order of each set, combinations (which are fewer) do not. pgc01's macro applies there:

http://www.mrexcel.com/forum/excel-...c-applications-combinations-permutations.html

 

[B___P]

In my post there was a typo.
Assuming you start in A1 the correct formula is

=MOD(ROUNDUP(ROW(A1)/(3^(COLUMN()-1)),0)-1,3)+1

Where 3 counts the elements you shuffle


For your last question you have to put together
=MOD(ROUNDUP(ROW(A1)/(4^(COLUMN()-1)),0)-1,4)+1
4 because 5, 10, 15, 20 are 4 elements

and

=CHOOSE(1,5,10,15,20)
where 1 is the index and the rest is the list of elements to shuffle

the result is

=CHOOSE((MOD(ROUNDUP(ROW(A1)/(4^(COLUMN()-1)),0)-1,4)+1),5,10,15,20)

Bye