How can I solve the problem by solver (with attachment)

**lubbamkt** · 10-08-2013, 11:52 PM

Hi all,

I attached an excel file in which I wanna achieve a result to get the closest average price for each customer.

Details:
There are total quantity of 300 shares for the same stock ordered eqaully by 3 customers (A,B,C).
The acutal executed orders are in the lower part of worksheet1. 300 shares were bought in total of 6 transactions.
I have to allocate the executed orders to the 3 customers and ideally the average purchase prices for them are as closer as possible. One constraint is that the allocation of shares must be in a mulitple of 50. For example, to customer A, the average price must be derived from any two in combination ( $1, $1.1, $0.95, $1.2, $1.15 and $1) though the weighted average price is $1.0666 for the total 300 exexcuted orders.
I wanna achieve my goal by using Solver. Can anyone help? Thanks in advance

**lubbamkt** · 10-09-2013, 02:54 AM

Can anyone help? Please

**MrShorty** · 10-09-2013, 10:19 AM

I generally find that these kinds of problems are easier if I understand the math behind the problem first. So, the first step I usually start with is a basic statement of what the function is I want to use. In this case, I notice that your "set target cell" and "by changing cells" in the Solver model are empty. When I am building something to solve with Solver, I usually follow these steps:

1) Derive a reasonable function for the problem. Ideally this will be a "contiinuous" function (no breaks, gaps, or other discontinuities). Ideally, there will also be only one solution, though it can often be difficult to force only one solution.
2) I will usually spend some time with the function becoming familiar with it. Sometimes I will build a table and or graph of the function so I can understand what the function is doing. This will include identifying where there are multiple solutions, where there might be errors, and where there might be other problem areas. I am also looking for help in selecting appropriate "seed" values or "initial guesses" to initiate the Solver algorithms.
3) Once I have a feel for how the function behaves, then I can enter the appropriate seed values I selected in step 2 and call Solver and set up the Solver model to find the solution.

I realize that is a lot of talk, and nothing specific, but I'm afraid I do not understand your specific problem well enough to suggest specific ideas.