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
9If 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)?
1 Answer
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)
