• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dividing a set of numbers to be as equal as possible

M

mdsuhair

New Member
I have aset of numbers

A=1

B=2

c=3

D=4

E=5

F=6

G=7

What formula do i use in excel to divide the A,B ....G into 2 groups so that the sum of the numbers in each of the 2 groups is as equal as possible
 
This is a great question where Solver can be used to answer the question!


I have uploaded a mockup as an example to use to describe below

https://rapidshare.com/files/458585647/MDSUHAIR.xlsx


Next to the 2 columns A-G and Numbers, I have put 2 Columns, Bucket 1 and Bucket 2

Next to that Total

And then Score 1 and Score 2


Bucket is a simple 0 or 1 to indicate if the number is included (1) or not (0)

Total is the Total of Bucket 1 and Bucket 2

Score 1 and Score 2 are the Number x Bucket 1 and Bucket 2 respectively


Next I have added a Total at the base of Score 1 (G10) and Score 2 (H10)

and a Difference in H11: = H10-G10


Open Solver from the Data, Analysis tab

If it is not loaded goto the File, Options, Addins, Manage Excel Addins and select Solver


The following are the Objectives and Constraints


Our objective is to have H11 =0 subject to a number of constraints

The Constraints are:


the Buckets must have an Integer value

the Total Column must add to 1


Thats It


Put 0 in the buckets manually and then Run the Solver again


A further constraint may be to add a count of the numbers in each bucket and then minimise the difference between them, this would force the buckets to have a more similar number of items in each


Hope that helps
 
Thanks Very much Hui. i am having problems downloading using rapid share. any other way u can attach the file?? thanks so much
 
Click on the above link

There should be a grey Slow Download button on the right side of the Green High Speed Download button
 
Thanks so much. i understand the algorithm and tried it on my set of numbers with a few additional constraints and it works fine altho it takes apprx 20 minutes to solve. thanks for the help Hui.
 
Hui..worked fine with small number of entries, expanded a little bit and now 3 issues

a) solver time is forever > 3 hrs sometimes b) different final results c) splits the item into 2 buckets even though the constraint says bucket 1 or 2

https://rapidshare.com/files/1229991255/LD82290_algorithm.xlsx. can u pls help
 
You must log in or register to reply here.
Back
Top