Solver gotchas
 

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

Solver gotchas
 

Hi.

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

I don't believe this is true. The 'Array' function "Sumproduct()" is a
common function used in the Target Cell.

does it work if some of the cells
involved contain user-defined VBA functions?

Yes. It works. One just has to make sure the function is not
Discontinuous. For example, don't use Max, Min, IF(), Abs()...etc.
This applies to the worksheet also.

= = =
Good luck. :)
Dana DeLouis



G.R. Toro wrote:
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

Solver gotchas
 

Dana,

Thanks for your response. Regarding array formulas, I am concerned about
the ones that one enters using CTRL+SHIFT+ENTER and show up enclosed in {}
braces. In that sense, a formula including sumproduct() is not necessarily
an array formula.

Thanks,

Gabriel


"Dana DeLouis" wrote in message
...
Hi.

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

I don't believe this is true. The 'Array' function "Sumproduct()" is a
common function used in the Target Cell.

does it work if some of the cells
involved contain user-defined VBA functions?

Yes. It works. One just has to make sure the function is not
Discontinuous. For example, don't use Max, Min, IF(), Abs()...etc.
This applies to the worksheet also.

= = =
Good luck. :)
Dana DeLouis



G.R. Toro wrote:
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

Solver gotchas
 

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 wrote:
Dana,

Thanks for your response. Regarding array formulas, I am concerned about
the ones that one enters using CTRL+SHIFT+ENTER and show up enclosed in {}
braces. In that sense, a formula including sumproduct() is not necessarily
an array formula.

Thanks,

Gabriel


"Dana DeLouis" wrote in message
...
Hi.

I seem to recall that solver does not work when the objective function
contains an array formula. Can anyone confirm this?
I don't believe this is true. The 'Array' function "Sumproduct()" is a
common function used in the Target Cell.

does it work if some of the cells
involved contain user-defined VBA functions?
Yes. It works. One just has to make sure the function is not
Discontinuous. For example, don't use Max, Min, IF(), Abs()...etc.
This applies to the worksheet also.

= = =
Good luck. :)
Dana DeLouis



G.R. Toro wrote:
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

Solver gotchas
 

Thank you.

Gabriel

"Dana DeLouis" wrote in message
...
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 wrote:
Dana,

Thanks for your response. Regarding array formulas, I am concerned about
the ones that one enters using CTRL+SHIFT+ENTER and show up enclosed in
{} braces. In that sense, a formula including sumproduct() is not
necessarily an array formula.

Thanks,

Gabriel


"Dana DeLouis" wrote in message
...
Hi.

I seem to recall that solver does not work when the objective function
contains an array formula. Can anyone confirm this?
I don't believe this is true. The 'Array' function "Sumproduct()" is a
common function used in the Target Cell.

does it work if some of the cells
involved contain user-defined VBA functions?
Yes. It works. One just has to make sure the function is not
Discontinuous. For example, don't use Max, Min, IF(), Abs()...etc.
This applies to the worksheet also.

= = =
Good luck. :)
Dana DeLouis



G.R. Toro wrote:
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