+ Reply to Thread
Results 1 to 7 of 7

How to cycle through combinations?

  1. #1
    Registered User
    Join Date
    01-10-2020
    Location
    São Paulo
    MS-Off Ver
    2016
    Posts
    4

    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

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,233

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    01-10-2020
    Location
    São Paulo
    MS-Off Ver
    2016
    Posts
    4

    Re: How to cycle through combinations?

    Quote Originally Posted by XOR LX View Post
    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!
    Last edited by hrmantovani; 05-29-2020 at 02:37 AM.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,233

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

  5. #5
    Registered User
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    2007, 2010, 2019
    Posts
    65

    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.

    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.
    Przemyslaw Kowalik, Lublin Univ. of Technology

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,062

    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.

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,233

    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
    Last edited by XOR LX; 05-29-2020 at 09:11 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 08-25-2017, 03:46 AM
  2. [SOLVED] cycle iif vba
    By lupingamon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-26-2014, 08:56 AM
  3. Finding possible combinations & listing the wanted combinations
    By Zoke in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-16-2012, 03:41 PM
  4. VBA Cycle Help
    By Paul.127 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-21-2011, 08:39 AM
  5. cycle for
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-07-2009, 09:30 AM
  6. cycle Columns, how?
    By iori in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2006, 08:44 PM
  7. [SOLVED] How do I keep result from 1 iteration cycle to use in next cycle?
    By sgl8akm in forum Excel General
    Replies: 0
    Last Post: 07-27-2006, 03:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1