Problem with Solver and Integer constraints

[dave.rafkind]

If the solver is attempting to optimize a cell with a function in it,
and the input cell to the function has an integer constraint on it, the
solver won't do anything.

For example:

set A1 = 1
set B1 = "=dummy(A1)"

create a function in a module like so:

public function dummy( x as integer ) as integer
dummy = x * 10
end function

optimize for the maximum value of B1, constraining A1 like so:
A1 >= 1
A1 <= 10
A1 is an integer

...And the solver does nothing. What is the proper way to do this?

 

[Tushar Mehta]

You are trying to optimize a discontinuous function. Dummy(1<=x<1.5)=
10 and Dummy(1.5<=x<2.5)=20.

Solver, like several other optimization programs doesn't work well with
discontinuous functions (actually non-smooth functions). Change Dummy
to

Function Dummy(x As Double) As Double
Dummy = x * 10
End Function

and everything will work just fine. You will get 10 as the solution.

--
Regards,

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