Problem with Solver and Integer constraints

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?

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

In article <1123787319.948869.263490@f14g2000cwb.googlegroups.com>,
dave.rafkind@gmail.com says...
> 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?
>
>

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

In article <1123787319.948869.263490@f14g2000cwb.googlegroups.com>,
dave.rafkind@gmail.com says...
> 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?
>
>

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

In article <1123787319.948869.263490@f14g2000cwb.googlegroups.com>,
dave.rafkind@gmail.com says...
> 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?
>
>

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

In article <1123787319.948869.263490@f14g2000cwb.googlegroups.com>,
dave.rafkind@gmail.com says...
> 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?
>
>