# How to cycle through combinations?

1. ## How to cycle through combinations?

I made a table consisting in four main columns, from B to E, and 14 named products. Supposing I can only handle 5 at a time, a formula calculates the efficiency of the combination (based on data that's not in the image). The column B is what triggers the calculation: typing "1" makes the product an active part of the combination.

Remember I need to have 5 activated at a time and always 5. Is there a way to make Excel cycle through combinations? Combining products A, B, C, D and E gives 47,5% efficiency, but there are 2002 combinations, so I wonder if Excel can do that for me, because I wanted to know which combination gives me the best average efficiency.

Excel.JPG  Register To Reply

2. ## Re: How to cycle through combinations?

Hi,

For small numbers of permutations it's possible using worksheet formulas alone. If you post a sample workbook (using dummy data if necessary) then I'll show you how. The yellow box at the top of the page tells you how to attach files.

Regards  Register To Reply

3. ## Re: How to cycle through combinations? Originally Posted by XOR LX Hi,

For small numbers of permutations it's possible using worksheet formulas alone. If you post a sample workbook (using dummy data if necessary) then I'll show you how. The yellow box at the top of the page tells you how to attach files.

Regards
I'm not sure if 2002 permutations is a small number, but maybe for Excel it is. I made an example sheet removing those columns with state and country because it's just visual stuff, not used for anything.
This is the example table I came up with:

Edit: I have just found a way to do it! Thanks for the attention!  Register To Reply

4. ## Re: How to cycle through combinations?

Ok, I wasn't expecting such a complex formula as the one you have in cell D21. I'll have to leave it to you to adapt the following to meet your requirements:

The following array formula** will cycle through and sum all 2002 combinations of 5 values from the range AN4:AN18, then return the maximum of those 2002 sums:

=MAX(MMULT(N(OFFSET(AN4,IF(MOD(INT((MODE.MULT(IF(MMULT(MOD(INT((ROW(INDEX(A:A,1):INDEX(A:A,2^14))-1)/2^(COLUMN(\$A:\$N)-1)),2),ROW(\$1:\$14)^0)={5,5},ROW(INDEX(A:A,1):INDEX(A:A,2^14))-1)))/2^(COLUMN(\$A:\$N)-1)),2),COLUMN(\$A:\$N)),)),ROW(\$1:\$14)^0))

Obviously you can change the reference (AN4) so as to give results for other columns.

You'll probably have to breakdown your current formula for Usage into smaller parts and incorporate versions of the above.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).  Register To Reply

5. ## Re: How to cycle through combinations?

@XOR LX - great formula! But it's still necessary to include all that EXP stuff from D21.
I tried Solver instead because at a first glance it looks like as a job just for it.

Solver settings

Set Objective D24
To Max
By Changing Variable Cells B5:B18
Subject to the Constraints
B19=5
B5:B18 = binary

Unfortunaly, the problem is very complicated with regard to the objective function and Solver doesn't perform well.

The GRG Nonlinear methid seems to get stuck to any feasible solution it starts from. When starting from zeroes as initial values for B5:B18, it finds a solution with 1's in B14:B18 (last 5 cells) with Average efficiency = 33.4. Very bad, especially if we notice that with 1's in B5:B9 (first 5 cells) we have Average efficiency = 47.5.

When I tried the Evolutionary method, I obtained, after several Solver runs, the best Average efficiency = 50.4 for 1,1,1,0,1,0,0,0,0,1,0,0,0,0 (of course not sure if really the best). But it may be a matter of good luck. Other results for Evolutionary were 48.8 and 49.2.  Register To Reply

6. ## Re: How to cycle through combinations?

As the form is a place to learn, it is not good to remove the table as others can not follow the solution provided. You should also share your own solution to help others.  Register To Reply

7. ## Re: How to cycle through combinations?

Ok, here goes!

First, some definitions in Name Manager:

Name: Comb_Array
Refers to: =IF(MOD(INT((MODE.MULT(IF(MMULT(MOD(INT((ROW(INDIRECT("1:"&2^14))-1)/2^(COLUMN(\$A:\$N)-1)),2),ROW(\$1:\$14)^0)={5,5},ROW(INDIRECT("1:"&2^14))-1)))/2^(COLUMN(\$A:\$N)-1)),2),COLUMN(\$A:\$N))

Name: AN_Sums
Refers to: =MMULT(N(OFFSET(Efficiency!\$AN\$4,Comb_Array,)),ROW(\$1:\$14)^0)

Name: AO_Sums
Refers to: =MMULT(N(OFFSET(Efficiency!\$AO\$4,Comb_Array,)),ROW(\$1:\$14)^0)

Name: AQ_Sums
Refers to: =MMULT(N(OFFSET(Efficiency!\$AQ\$4,Comb_Array,)),ROW(\$1:\$14)^0)

Name: AR_Sums
Refers to: =MMULT(N(OFFSET(Efficiency!\$AR\$4,Comb_Array,)),ROW(\$1:\$14)^0)

Name: AS_Sums
Refers to: =MMULT(N(OFFSET(Efficiency!\$AS\$4,Comb_Array,)),ROW(\$1:\$14)^0)

After which the required array formula** is:

=MAX((((5/(1+EXP(-(3*(AN_Sums-2)))))+(1/(1+EXP(-(15*(AO_Sums-1.75))))+1/(1+EXP(-(5*(AO_Sums-2.75)))))+(3/(1+EXP(-(15*(AQ_Sums-0.75))))+1/(1+EXP(-(5*(AQ_Sums-1.75)))))+(1/(1+EXP(-(15*(AR_Sums-0.75)))))+(3/(1+EXP(-(15*(AS_Sums-0.75)))+1/(1+EXP(-(5*(AS_Sums-1.75)))+1/(1+EXP(-(5*(AS_Sums-2.75))))))))/17)*(0.5+0.5*(IF(SQRT(1+AN_Sums+AQ_Sums+AS_Sums)-1<0,0,IF(SQRT(1+AN_Sums+AQ_Sums+AS_Sums)-1>2,2,SQRT(1+AN_Sums+AQ_Sums+AS_Sums)-1))/2)))*100

This is a straight adaptation of the original formula. I'm sure it could be simplified, though I'll leave that to the OP!

The above returns 61.6683 to 4 d.p., and it can be verified that this is the same result as given by the OP's original formula, with cells B5, B7, B10, B14 and B17 marked with a 1.

Regards  Register To Reply