## User defined Function and Solver in Excel

## User defined Function and Solver in Excel

(OP)

I have encountered a problem with using Solver to optimize some of the inputs of a user defined function. I would like to use Excel's Solver to minimize the sum of the square errors between observed data and a model.

I read some other posts or threads that dealt with the same issue. I believe that I am passing the inputs through the user defined function that I am trying to get Solver to adjust. Which was the issue with ExcelSolverUser's post.

I believe that I am having a similar issue that yngeng33 originally described. When I use a workaround, I can use solver to minimize the sum of square error between the model and trial data. However, when I use my user defined function to define the model output, solver will indicate that it has found an optimum solution when in fact it is far from an optimal solution ( i.e. I can manually adjust the cells to decrease the sum of square error between the model output and trial data).

I have attached a file that contains the user defined function, model output and trial data for your viewing.

Any advice on this issue would be greatly appreciated!

I read some other posts or threads that dealt with the same issue. I believe that I am passing the inputs through the user defined function that I am trying to get Solver to adjust. Which was the issue with ExcelSolverUser's post.

I believe that I am having a similar issue that yngeng33 originally described. When I use a workaround, I can use solver to minimize the sum of square error between the model and trial data. However, when I use my user defined function to define the model output, solver will indicate that it has found an optimum solution when in fact it is far from an optimal solution ( i.e. I can manually adjust the cells to decrease the sum of square error between the model output and trial data).

I have attached a file that contains the user defined function, model output and trial data for your viewing.

Any advice on this issue would be greatly appreciated!

## RE: User defined Function and Solver in Excel

I can't read the file you posted in my excel 2000... maybe save as xls.

I have user-defined functions as part of the logic of a solver model that I use.... haven't had problems.

It may be unrelated, but I have observed that if I have a U.D.F. that retrieves data from the spreadsheet directly inside the UDF (rather than passing the data as an argument), then excel is not smart enough to figure this out and sometimes does not recalculate when those cells change. So maybe a good check is that any spreadsheet data used by your u.d.f. is passed through as an argument.

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

As for the threads that I was referring to (I am not sure if there is a better way to give you this information). The threads I was referring to were:

http://www

and

http://www

Cheers!

## RE: User defined Function and Solver in Excel

If that's the problem, the solution may lie in getting a better/closer initial guess.

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

You might try Goal Seek with just G11, it seems to get closer than the solver. Goal Seek wound up with G11 = 4655.3, and sum of residuals squared of 5.23E7

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: User defined Function and Solver in Excel

You may then ask, why do you need to have Solver work with your UDF if you have this workaround. My answer is, for this model I do have a work around however, I have another model that is very similar and I do not have the same workaround.

Later

## RE: User defined Function and Solver in Excel

Are you positive it is the same model for your udf spreadsheet as your spreadsheet formula spreadsheet?

Are you positive that you have used similar initial guess during both solver trials. Perhaps to double-check you could put same inputs to both spreadsheet and get same outputs. Also put near-optimimum solution based on your spreadsheet-formula model solver solution into your udf model solver initial conditions.

Attached are 3 experiments. You can run them by following instructions in red for each experiment.

In experiment 1 we have simple formula y=x^2, and we try to guess the values of several x to match the target by minimizing sum of squares of error. When we set the initial guess (for example x2 and x4) to zero, solver doesn't move them because the initial derivative of the objective function with respect to those inputs is close to zero. (surprisingly to me, solver does solve it with zero initial conditions if you change option from forward derivative to central derivative). The conclusion in this case is that failure of solver is due to initial conditions... there are no array formula's.

In experiment 2 we have simple formula which outputs y=x^2 only when x<10, but clamps y at 10 when y>10. When we have initial guess > 10 (for example x2 and x4), solver does not move those variables. The reason is that the derivatives that it computes about the initial conditions are 0. The conclusion in this case is that failure of solver is due to initial conditions... there are no array formula's.

In experiment 3, we have an array udf... similar to the structure that you used. It works fine. I conclude there appears to be nothing inherent in the array udf that will fool solver (as long as inputs are passed as arguments).

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

Now going back to experiment 3, I see it fell victim to the same problem. If you turn off the option "assume non-negative", then experiment 3 does just fine.

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

The explanation for experiment 1 having a problem was that the input x2 and x4 were stuck on a relative maximum where it could go either way to reduce SSE... they decided to go toward lower (negative) values but were blocked by the non-negative constraint.

Experiment 3 behaves almost identical to experiment 1. As long as we have good initial values that don't land us against a tricky relative maximum constraint, it does fine.

Experiment 2 is fooled due to initial values that cause derivative of objective function equal to zero.

You also definitely have this same condition as experiment 2 present in your spreadsheet because (with the initial conditions you gave) manually varying some of the inputs had no effect whatsoever on the objective function. I'm not sure whether you may have also been locked against some constraints?

Sorry for the rambling.

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

So the difference was simply that the sequence of guesses that solver went through during experiment 3 landed it in a bad place (x2=0) where it couldn't recover. But different selection of initial values (try x2=4) or constraints (remove non-negative constraint) would still solve it.

=====================================

(2B)+(2B)' ?

## RE: User defined Function and Solver in Excel

## RE: User defined Function and Solver in Excel