6

I've got a column of numbers, and I'd like to find the best combination of three of these numbers. In our case, the best combination is a sum that is as close as possible to our goal number.

For example, we have a goal of 100 and this column of 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 best three-number combination (something like recursive sum distribution)?

Improve this question

1 Answer 1

Reset to default
16

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

Improve this answer
3
  • 3
    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, 2011 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, 2011 at 22:46
  • thank you for the wonderful post, Is there a VBA code for this theory?
    – A. Kiyoshi
    Sep 20, 2019 at 0:04

You must log in to answer this question.