+ Reply to Thread
Results 1 to 14 of 14

How to divide a set of numbers into 2 equal sum groups

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    How to divide a set of numbers into 2 equal sum groups

    I have a set of data. example
    A=1, b=2,c=3, d=4,e=5,f=6,g=7.
    I want to divide into 2 groups so that the sum of both groups are as equal as possible

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,151

    Re: How to divide a set of numbers into 2 equal sum groups

    Hi mdsuhair and welcome to the forum,

    I love these problems. Here is how I solve them. I get the list of numbers and in the column to the right I create a random number. Then I sort by the random number column which sorts the given numbers in a random order. Then I add the top half and bottom half to see if their sums are equal. If not I do it again. I need to press the recalculate button a few times until the two sums are equal and you have the answer.

    If you need to see this in practice, you need to attach a sample file so we can see your real problem and data structure. Please put what is given and what you expect to have as the outcome.

    To attach a file, click on "Go Advanced" below the message area and then on the Paper Clip Icon above the message area to attach a sample file.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Registered User
    Join Date
    04-21-2011
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to divide a set of numbers into 2 equal sum groups

    Marvin..thanks for ur response. I have attached a file and perhaps you could try out ur idea and get back to me with ur methodology via an example, again thanks and i do appreciate
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,151

    Re: How to divide a set of numbers into 2 equal sum groups

    Hi,

    Find the attached with how I do it. I typed instructions in the workbook. If it doesn't make sense then let me know.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-21-2011
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to divide a set of numbers into 2 equal sum groups

    Thanks Marvin. I will give it a try this weekend to see if i understand. thanks so much for ur help,

  6. #6
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Mexico
    Posts
    200

    Re: How to divide a set of numbers into 2 equal sum groups

    Try this one:
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-21-2011
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to divide a set of numbers into 2 equal sum groups

    Thanks Marvin. I found a excel solver method essentially creating 2 buckets which willbe either1 or 0 for each line item and put some constraints to minimize the difference between the buckets, and it works fine; think this approach is more intuitive and will help my team offshore het going quickly
    thanks all the same, really appreciate ur help

  8. #8
    Valued Forum Contributor nimrod's Avatar
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: How to divide a set of numbers into 2 equal sum groups

    This solution was tested with 100 random numbers (see attachment). It completed the problem in under 1 second and a single pass of the numbers.

    ASSUMPTION:This solution assumes that the resulting 2 groups of numbers must be equal in sum value BUT it does not assume that the groups must have equal number of members. Since the orginal requirements does not indicate groups have to have same number of members.
    This solution also assumes it's OK to re-sort the numbers.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    YOUR FEEDBACK: To Say Thanks, or to leave Constructive Comments, please click on the "* Add Reputation" link below.

    >Develope Good Habits with MSDN Coding Standards <>How To Add Macros & VBA Code To Your Workbooks<>Best Practices For Referencing Cells, Ranges and Sheets<

  9. #9
    Registered User
    Join Date
    04-21-2011
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to divide a set of numbers into 2 equal sum groups

    Marvin... i tried the excel solver and have run into execution time and accuracy problems. maybe my set up is wrong. can u pls look at my attached file. i am trying to sort the column a ( which is an identifier for the item) . each item in column A contains a certain number of widgets so # 4 has 522 widgets. i want the items arranged in 2 buckets so that the difference between the 2 buckets is a minimum ( the difference between 2 buckets represents wastage). To minimize wastage i am prepared to take a reserve ( 0 or 1, or 2 items in practical terms). What i would like to do is to use ur solver
    Attached Files Attached Files
    Last edited by mdsuhair; 05-12-2011 at 01:42 PM. Reason: forgot to add remark

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,151

    Re: How to divide a set of numbers into 2 equal sum groups

    Hi mdsuhair,

    I really never used the Excel "Solver" to do any of my calculations. I simply used a random number generator and did a lot of guessing. I believe the Excel Solver needs a formula of some type to attack the problems. My method is much stupider than that. It just guesses and checks the final answer to see if it is better than the best past guess(s). If the answer is better, then it copies the Items, Qty, and buckets to a different spot on the worksheet and keeps trying. Calling my method a solver is really extending what it does. It simply randomly picks buckets and adds the Qty and sees if the answer is better than the best one so far. I have very little logic in the code, other than the final check.

    I find that the tool (called computer) can guess a lot of times and a formula might not be needed. Before computers we needed formulas to arrive at answers. I'm kind of going back to pre-logic days and simply trying answers.

    I hope this helps you understand the method. Read my past code and modify it a bit to make it work for your new problem.

  11. #11
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,165

    Re: How to divide a set of numbers into 2 equal sum groups

    I've set up a Solver solution in sheet "LDB2290_1 reserve". I changed the setup of the object function and simplified the constraints a bit. I've also changed the Solver option by ticking the box "Asume Non-Negative" and increased the max time to 200 seconds.

    Have a look and see if this fits your need.

    If speed is your main concern stick to nimrod's solution because Solver realy took time in solving this problem. Perhaps a futher triming of options may increase Solver's speed but in no way it will match
    It completed the problem in under 1 second and a single pass of the numbers.
    nimrod's result.

    HTH

    Alf
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-29-2010
    Location
    texas
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: How to divide a set of numbers into 2 equal sum groups

    Hi Nimrod,

    thanks for the above. Is there a way to do the same, but such that the two groups would:

    1. Sum to the same total (or close to it)
    2. The groups would be rankes, meaning Group A would be values Min(data) up to X, and Group B would be X+1 up to Max(data).
    3. Could this be done for more than one group. i.e. segment three groups as above, each summing to a third of the total sum, or equvalently, each summing to the same number?

    Thanks for any help on this!

  13. #13
    Registered User
    Join Date
    10-22-2014
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    1

    Re: How to divide a set of numbers into 2 equal sum groups

    Quote Originally Posted by nimrod View Post
    This solution was tested with 100 random numbers (see attachment). It completed the problem in under 1 second and a single pass of the numbers.

    ASSUMPTION:This solution assumes that the resulting 2 groups of numbers must be equal in sum value BUT it does not assume that the groups must have equal number of members. Since the orginal requirements does not indicate groups have to have same number of members.
    This solution also assumes it's OK to re-sort the numbers.

    Please Login or Register  to view this content.
    Hello. I am looking at your excel sheet and it works perfect for my purposes, however, I was wondering what would be involved to increase the number of groups from 2 to say 4 or 6? I am not good with the code, but looking at it and it's assumption of half the data, I would assume it would take lot's of work to make it work with any other number of sets correct?

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    46,907

    Re: How to divide a set of numbers into 2 equal sum groups

    Hi ehblend, welcome to the forum

    2 things...
    1. This thread over 2 years old, I doubt anyone is still following it.
    2. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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