solver help

[dscarter7]

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
etc
1,000 -20 4.2% 1 etc 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
D14, 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)

Jerry

 

[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

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions