# How to divide a set of numbers into 4 equal sum groups

1. ## How to divide a set of numbers into 4 equal sum groups

Hi, I would like to develope a VBA macro code to split pool of numbers into 4 groups of equal / minimum different of sum.
We could exclude or left out one or two number that cannot fit.

Example of my attachment: I have 25 numbers need to be splitted into 4 groups of equal sum.

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

I would venture to suggest that much of this question is more about the algorithm than about the code. Do you have a preferred algorithm for this problem?

From a quick look around the internet, I see a few different algorithms proposed.

1) A Monte Carlo type algorithm. Put together random groupings, check the sum, then randomly swap values from one group to another until you find a suitable solution. With the right thinking, you can probably make this more of an evolutionary algorithm so that it tends towards a better solution with each iteration rather than being truly random.

2) Something like this one looks promising: http://answers.microsoft.com/en-us/o...6-b1b529cc6bcc I tried it using Excel functions and it seems fairly straightforward. Sort the data from high to low or low to high. In an adjacent column, put 1,2,3,4,1,2,3,4,..... Then, the formula for each stack becomes a simple =sumif() function. Adjust the criteria for the sumif() until the sums are suitably close together.

3. ## Re: How to divide a set of numbers into 4 equal sum groups

hi ahchin5 and welcome to the forum.

as you've no doubt discovered this is a rather complicated request. i looked around for a while but didn't find anything i considered perfect so i wrote some code which is also not perfect, but it's almost the weekend and it doesn't look like you're getting a better answer for now:

``Please Login or Register  to view this content.``
basically this just sorts the values largest to smallest and then drops them one at a time into each of your four buckets (excluding the smallest value at the end...) - the end result is not bad. i had some thoughts around identifying how far from the average each of the buckets is and trying to trade some values around to optimize them and / or looping through each individual value to see if moving it to another bucket would reduce the overall variance, but i didn't actually make any headway in the vba. Even with the additional effort to tweak the thing i don't think i can guarantee an optimal distribution though...

anyway, after all that, i thought perhaps this code would get you close enough to provide some value - from here you can fairly easily do a spot check and identify additional optimization opportunities so at least you're not just starting from scratch. Hope it helps.

4. ## Re: How to divide a set of numbers into 4 equal sum groups

It is also posible to use solver and I've setup a model for this. As you have 25 items that you wish to split i four groups I've specified one group with 7 items and the other 3 with 6 items each.

To test clear range D2:G26 and run solver.

For setting up solver and how it works see link to "Chandoo.org"

http://chandoo.org/wp/2011/05/11/usi...o-assign-item/

Alf

5. ## Re: How to divide a set of numbers into 4 equal sum groups

Hi Alf,
Thank you very much. In fact I tried solver but the solution seems not suit to my need. It do not match and group into 4 with minimal difference in sum (which is causing waste of my material in production). That is why I need to split our "reserved wafer" for future use if any of it cause big difference in the sum of 4 groups.

6. ## Re: How to divide a set of numbers into 4 equal sum groups

Hi simarui,

Thank you very much for your great help! This seems close to my need but as you mentioned it is still not optimize to split with smallest variance.
In fact I need this for my production to split my product builds correctly to eliminate waste.
If you have any better idea on how to optimize it later on, please kindly help me.. thanks a lot

8. ## Re: How to divide a set of numbers into 4 equal sum groups

I got this close using Evolutionary Solver, changing col C to minimize max - min:

 Row\Col A B C D E F G H I 1 Wafer Qty Stack 1 2 3 4 2 0 0 0 0 3 wf01 522 2 0 522 0 0 D3: =D2 + (ROUND(\$C3, 0)=D\$1)*\$B3 4 wf02 526 4 0 522 0 526 5 wf03 542 2 0 1064 0 526 6 wf04 530 3 0 1064 530 526 7 wf05 579 1 579 1064 530 526 8 wf06 529 4 579 1064 530 1055 9 wf07 483 3 579 1064 1013 1055 10 wf08 477 3 579 1064 1490 1055 11 wf09 578 2 579 1642 1490 1055 12 wf10 558 1 1137 1642 1490 1055 13 wf11 522 3 1137 1642 2012 1055 14 wf12 430 3 1137 1642 2442 1055 15 wf13 571 4 1137 1642 2442 1626 16 wf14 554 2 1137 2196 2442 1626 17 wf15 504 2 1137 2700 2442 1626 18 wf16 528 4 1137 2700 2442 2154 19 wf17 579 2 1137 3279 2442 2154 20 wf18 560 1 1697 3279 2442 2154 21 wf19 478 3 1697 3279 2920 2154 22 wf20 572 4 1697 3279 2920 2726 23 wf21 522 4 1697 3279 2920 3248 24 wf22 499 3 1697 3279 3419 3248 25 wf23 565 1 2262 3279 3419 3248 26 wf24 531 1 2793 3279 3419 3248 max-min 27 wf25 557 1 3350 3279 3419 3248 171

9. ## Re: How to divide a set of numbers into 4 equal sum groups

Hi Alf, yes this is exactly what i wanted.

10. ## Re: How to divide a set of numbers into 4 equal sum groups

Your problem in Solver terms is a “Simplex LP” problem but the solver model that comes with Excel is not powerful enough to solve this.

To do so you need to download the “OpenSolver” for Excel. You can either build your model in “OpenSolver” or using the Excel solver (I prefer the latter as I’m more used to this).

The setup is quite simple but depends a bit on your Excel version. Forum profile says Excel 2007 but your uploaded file is xls i.e. Excel 2003 or earlier.

The image shows the solver model for Excel 2007. In the upper right corner the icons for “OpenSolver” can be seen. The solver settings under “Options” are “Tolerance: 1%”, “Assume Linear Model” and “Assume Non-Negative” boxes ticked.

Alf

11. ## Re: How to divide a set of numbers into 4 equal sum groups

hi Alf, could you please send the Excel solver that you did as screenshot (with the Reserved) to me?
I tried to put in the criteria as the picture but came out error

12. ## Re: How to divide a set of numbers into 4 equal sum groups

could you please send the Excel solver that you did as screenshot
Sure no problem but rememder you need to download the "OpenSolver" and extract the files. Then open this file and find the folder where the "OpenSolver" is stored normally "C:\Opensolver". In this folder you will find the file "OpenSolver.xlam". Doubble click on this file and then you will have "Opensolver" on the ribbon of the opened Excel file. You have acccess to "OpenSolver" as long as you have an active Excel file. So the next time you starts Excel you will have to activate "OpenSolver" as well if you wish to use it.

Assuming you are in the "Data" tab you can now clear range "D2:H26" and click on icon marked "solve" this will start the run of the "OpenSolver"

http://opensolver.org/installing-opensolver/

Alf

13. ## Re: How to divide a set of numbers into 4 equal sum groups

thank you very much!

14. ## Re: How to divide a set of numbers into 4 equal sum groups

You are welcome and I hope this works for you.

Alf

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

#### 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