up vote 5 down vote favorite
1
share [g+] share [fb]

I'm not so good in excel but i "think" my problem can be solved using excel.. I've got a column with numbers and i'd like to find the best combination of using these numbers. In our case the best combination is a sum of three of those numbers that is as close as it can get in our goal number.

For example, we have a goal of 100 and a column filled with numbers these numbers: 15 70 36 60 30 53 37 17 0 75 100 9

if i sum 30+70+0=100 this group of 3 numbers (30,70,0) is the best combination since it reaches our goal number, 100. We can also get other combinations like, 60+30+9=99 and so on with the remaining numbers.

Is there a way through excel (or anything else if you have in mind) that can list me the 3numbers combination (something like recursive sum distribution)?

link|improve this question
feedback

migrated from stackoverflow.com Nov 28 '11 at 20:46

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 10 down vote

One method is by using Solver

  • Put your data in A1:A12
  • In B13, put a formula =SUMPRODUCT(A1:A12,B1:B12)
  • Set up solver so that B1:12 must be binary (ie 1 or 0)
  • In B14 put a "target" score, 100 in your example
  • in B15 put =ABS(B13-B14)
  • Set solver to look for the minimum value in B15 (to either give you an exact solution with no difference, or closest solution with smallest possible difference)

In this case the simplest solution is setting 100 to "on" (ie 1), all other values es "off" (0)

Screenshot for xl2003 for solving for 367 below (as this is more complex than 100)

enter image description here

link|improve this answer
+1 nice shot! and very good use of the solver – JMax Nov 27 '11 at 12:24
+1 nice answer and nice photo as usual – Issun Nov 27 '11 at 14:18
If I'm understanding the question, it also asked that the solution consist of three summed numbers. I'm not a Solver user, but in Excel 2010 I'm able to meet that constraint by adding a formula in B16 that counts the number of 1's in B1:B12 and setting the constraint for that B16 to 3. In addition, the Solving Method needs to be set to "Evolutionary" or it fails. I'm just hacking around though. Thanks for the inspiration. – Doug Glancy Nov 27 '11 at 16:19
@DougGlancy, thx for your addition Doug. Yes, your post is the best way to add the count of the constraint for 3 numbers. Some years back I used xl97 to pick a fantasy football team (aussie rules) using this exact method, optimising history value/fantasy point while solving for the constraint of picking certain number of defenders, mid-fielders, forwards etc. As you point out the Solver methods have changed over versions, so xl2010 does need the "Evolutionary" setting – brettdj Nov 27 '11 at 22:46
feedback

Your Answer

 
or
required, but never shown
discard