+ Reply to Thread
Results 1 to 10 of 10

Find a specific sum for two different arrays..(more explanation in the post)

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Find a specific sum for two different arrays..(more explanation in the post)

    Hello, everyone. Sorry for the informal introduction. I'm new to the forum and, I'm looking for some help

    For instance, the sum I'm looking for is 360. I am giving two arrays ( let's say Array A and Array B) both with a set of 24 numbers that could equal 360 if added together correctly. The function must choose 12 numbers from Array A and 12 from Array B to equal 360. How do go about writing out the equation/function? Thanks for your help and consideration. Cheers
    Attached Files Attached Files
    Last edited by bach1229; 10-02-2015 at 12:41 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    Solver found a solution pretty fast actually.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Registered User
    Join Date
    10-02-2015
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    Jacc,

    Thanks for your help. What exactly did state in the solver for Excel?

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    What exactly did state? Not sure what you mean.
    Solver is trying to make I25 equal to 360 by changing the numbers in range E2:E25 between 0 and 1, effectively saying which numbers in the left range that will be included. One constraint is that the sum of each column E2:E25 must be 12, hence 12 numbers of each column will be included.

    Solver is a smart algorithm but still a bit unpredictable, if it doesn't find a solution you can try and change the start values in range E2:E25. Just put a bunch of ones and zeroes in there, hit Solver button and pray.

  6. #6
    Registered User
    Join Date
    10-02-2015
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    Sorry, I didn't know how to explain it but thanks for the clarification.

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    @Jacc
    Nice model for solving this problem.

    Just wonder why did you choose the "Evolutionary" engine instead of the "Simplex LP" one?

    Alf

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    Thanks!
    I thought that was the one to use with non continuous function.
    So, which one should be used when and why?


    Edit: Just tried the Simplex LP on this problem. Darn it was fast!
    Last edited by Jacc; 10-03-2015 at 03:38 AM.

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    Just tried the Simplex LP on this problem. Darn it was fast!
    Yes, that's why I wonderd.

    So, which one should be used when and why?
    Linear problem i.e. y=k +ax Simplex, more complex one, curves with min and max or ocilating function equation of second or higher degree test with GRG non-liear and if you are using non contious functions try "Evolutionary" as you say.

    The which and the why, don't ask me I'm a Norwegian myself living in "excile" in Sweden. I allways starts testing if the "Simplex" work, if not then I try "GRG non-linear" and as a last resort the the "Evolutionary".

    Still even if you know a problem to be linear "Solver" may protest and tell you that "The liniarity condition required by this LP Solver are not satisfied." I have somtimes managed to fix this by ticking the box "Use automatick scaling" but this is a bit anoying not knowing why a model is found by Solver to be "non linear" when you know it is.

    How about Solver in Excel 2013, do find any improvements versus 2010?

    Alf
    Last edited by Alf; 10-03-2015 at 07:43 AM.

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Find a specific sum for two different arrays..(more explanation in the post)

    I'm not sure if there is any improvements for Solver in Excel 2013, I don't use it as much. I'm guessing it's faster if anything.
    Ah, thanks for the explanation. I guess this problem is as linear as they come then.

+ 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: 4
    Last Post: 07-20-2013, 11:25 AM
  2. [SOLVED] Add-a-row macros specific to arrays
    By wolfgang713 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2013, 01:54 PM
  3. [SOLVED] New to arrays-where do I find a good beginners guide to multi dimensional arrays
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-04-2013, 07:44 PM
  4. [SOLVED] Explanation on Arrays
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2013, 06:58 PM
  5. Opening a specific file on EXCEL startup - Better explanation
    By HitTheEXCELerator in forum Excel General
    Replies: 0
    Last Post: 05-28-2012, 02:20 PM
  6. Explanation of a formula used to find multiple returns
    By brownsl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-01-2009, 11:52 AM
  7. How to find intersectin of two arrays
    By joeriess in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2008, 11:17 AM
  8. [SOLVED] averaging specific rows in multiple arrays
    By GJR3599 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2005, 09:06 AM

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