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

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!

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).

@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.

I added =SUM(B5:B18) in B19

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.

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