Same functionality as Excel Solver to set objective to a value

Hi all

I am currently going through a process of taking an existing calculation perfomed within Excel and transferring it to MATLAB. Part of the existing calculation is to use Excel Solver to optimise two factors to allow a value to be calculated which matches a target value. This was achieved in the Excel solver by setting the objective to a fixed value using the 'Value Of' input box.

Going through the MATLAB optimisation functions I can only find solutions to minimise the value. My question is what function would I use to target a value? Note that I do not have the optimisation toolbox installed.

Many Thanks

Peter

Answer 1

[Alan Weiss]

I am not sure I understand your question. If you have a single function of a single variable, you can use fzero to solve for f(x) = a, by solving for a zero of the function f(x) - a.

However, you seem to have a single function of two variables, or perhaps you have two functions of two variables. If you have two functions of two variables, use fsolve to find a solution. If you have a single function of two variables, I don't understand what you or Excel can do to find a solution, because in general there are multiple solutions.

And I do not recommend using abs in Optimization Toolbox functions, this introduces unnecessary nonsmoothness. Use a square if you need to (but you probably do not need to).

Alan Weiss

MATLAB mathematical toolbox documentation

[Peter]

It is two functions and both use the same factors which I need to optimise. It sounds like I need to use fsolve. Thanks for both your help today.

Answer 2

[Titus Edelhofer]

Hi Peter,

to optimize a variable to be of value XTarget, you need to minimize the function

abs(x - XTarget)

If you don't have restrictions, you can use fminsearch as solver.

Titus

[Peter]

Thanks for the quick response Titus.

The existing spreadsheet I am using has a column of values (y) and a corresponding distance (x). Depending on the x location, y is either multiplied by Factor1 or Factor2. Another column of values (z) is calculated by multiplying the factored y value by the local distance x. The y and z values are then integrated and their totals are compared to a target value for both results.

Currently in the Excel solver the objective is set to the Y total cell. It has been set to match a target value by changing the cells containing the values for Factor 1 and 2. The constraint which was set is the z total value matches its target value.

With the constraints of matching the z total with a matching value, would I require another solver when using your suggestion of abs(x-Xarget) to find a minimum?