Help me solve this problem

dgbezerra

New Member
Joined
May 2, 2006
Messages
12
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
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/
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,027
Members
410,647
Latest member
bernardazar
Top