Solver gotchas

[G.R. Toro]

I apologize for the open-ended question.

I am about to embark on a complicated project where I need to use "solver"
and I am gathering some information before I jump in. BTW, I am Using Excel
2003.

I seem to recall that solver does not work when the objective function
contains an array formula. Can anyone confirm this?

Are there any other similar "gotchas" in solver. I am looking for this kind
of incompatibilities. For instance, does it work if some of the cells
involved contain user-defined VBA functions?

I am not asking about cases where the solver does not converge, takes too
long, or converges to a local (rather than global) peak. I know those are
facts of life in numerical optimization.

Thanks,

Gabriel

 

[Dana DeLouis]

a formula including sumproduct() is not necessarily

an array formula.

Hi. If I change the Target formula from:
=Sumproduct(A2:A6,B2:B6)

to:
=SUM(A2:A6*B2:B6) Ctrl+Shift+Enter

Solver works just fine.
So yes, an Array formula in the target is fine.
In fact, they are usually preferred for simplicity.
(depending on the model of course)

= = =
Dana DeLouis

 

[G.R. Toro]

Thank you.

Gabriel

Hi. If I change the Target formula from:
=Sumproduct(A2:A6,B2:B6)

to:
=SUM(A2:A6*B2:B6) Ctrl+Shift+Enter

Solver works just fine.
So yes, an Array formula in the target is fine.
In fact, they are usually preferred for simplicity.
(depending on the model of course)

= = =
Dana DeLouis