Help me solve this problem

[dgbezerra]

I am trying to find a way in excel to look up at a list of numbers associated with a name and find the best combination within this list that will be equal or close to a specific number that I would input at the bottom of the list. For example:

The list would be something like this: Stock performance list

Coca Cola 2%
Microsoft 6%
Bank of America 10%

Index 12%

*This is just an example. The real list would involve 1000 company stocks.

The excercise: To look at this list above and find using some sort of formula which combination would be equal or closest to the index result at the end of the year of 12%. From this example you can see that a possible solution would be the combination of Coca Cola and Bank of Ameirca would be equal to the index 12%. But in case that the list becomes 1000 stocks I have no idea how to use a formula to find this combination.

If you have suggestions please I greatly apprecite.

Diogo Bezerra

 

[Oaktree]

Hi, Diogo. In the future, please include more descriptive subject headings (as explained in the guidelines for posting here http://www.mrexcel.com/board2/viewtopic.php?t=131687). This will increase the visibility of your post as more people will look at it if its title is descriptive. For this example, maybe something like "determine the mix to sum to a value" or similar would have been good.

That said, this is the *type* of problem that Solver is ideal for. However, given your "changeable cells" (i.e. your 1000 stock variables) is well beyond the capacity of the solver add-in that is included with Excel, you would probably need to purchase one of the higher powered solver models available at http://www.solver.com/