solver help



I'm having problems with the solver. He is a simple example of what I'
trying to accomplish.

columnA columnB columnC columnD columnE columnF columnG
1,000 -50 3.0% 1 =A1*D1 =B1*D1 =C1*D1
1,000 -35 4.0% 1 =A2*D2 =B2*D2 =C2*D2
1,000 10 5.0% 1 etc etc
1,000 -20 4.2% 1 etc etc

I want to minimize the weighted avg of column G based on column E (ie
=sumproduct(G1:G4,E1:E4)/sum(E1:E4)). The cells I want to change ar
D1:D4, subject to the constraints of that they must be binary. I wan
the contents of column D to be either a 1 or 0. However, when I ru
solver, these numbers will become decimals. I've even tried puttin
additional constraints on column D, making them integers, less than o
equal to 1, and greater than or equal to 0. It produces the sam
outcome. Does anyone have a tip on how to solve this optimizer

Jerry W. Lewis

Solver looks at approximate partial derivatives of the function it is
trying to minimize. As such, it cannot work with discrete variables.

A weighted average (indeed any kind of average) satisfies
min(G1:G4) <= avg <= max(G1:G4)
so the weighted average is minimized by putting all weight on the
smallest observation (D1=1, D2=D3=D4=0)


Tushar Mehta

Three comments:

(1) Typically, Solver leaves the tableau with non-integer results when
it fails to find a solution. When you run Solver, how does it stop?
What message does the ending dialog box contain?

(2) If you don't have any constraints other than D being binary, the
solution, without the use of any optimization software, is D=0.

(3) You should be able to set this up as a linear problem. Linear
problems are significantly easier to solve. In the Solver dialog box,
click the Options button. In the resulting dialog box, select the
'Assume linear model' check box. Now, solve the problem


Tushar Mehta
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question