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