You have talked quite a bit about another thread but it's not linked, can you link it?

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)'  ?

As you requested, I have saved the excel file in the older version, and attached it.

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.eng-tips.com/viewthread.cfm?qid=220835&page=1

and

http://www.eng-tips.com/viewthread.cfm?qid=235500&page=3

Cheers!

• http://files.engineering.com/getfile.aspx?folder=94ed0d81-a2c6-4b23-976c-e4

I think you have a discontinuous function based on many if/then's.  That tends to cause problems because solver is computing a derivative... derivative of those variables which are not active will be zero so those variables will never change. It looks like in the initial condition the first two of your inputs (cells G9 and G10) are inactive because changing them has no effect on the output.

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

=====================================
(2B)+(2B)'  ?

Your model differs so much from your actual data that whatever numbers you come up with will be a poor fit to the measured data.

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

Thank you for the feedback thus far.  My issue is that I can use Solver to find an optimal solution if I don't use my user defined function (UDF).  Instead of using the UDF I have defined the model in a worksheet (it is exactly the same as the UDF but stores the intermediate calculations).  When I use the worksheet model, Solver arrives at an optimal solution.  However, when I use the UDF it appears that it does not change the values of the inputs at all.  This would suggest to me that it is not an issue with the model itself.  Instead, there is an issue with using Solver with my UDF.

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

I don't know the answer to your particular problem but I would suggest that you keep an open mind about your conclusion that the cause has something to do with use of UDF rather than spreadsheet functions.

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)'  ?

• http://files.engineering.com/getfile.aspx?folder=ae5c50bd-2886-4793-9039-14

Correction: Wherever I used the word "derivative", please substitute the phrase "partial derivative".

=====================================
(2B)+(2B)'  ?

Hmmm. When I reran experiment 3 with the exact initial conditions listed in the instructions (5,15,5,12), it does not solve x2 (it moves it, but not to the right place).  Then trying those same initial conditions in experiment 1 (which implements the same logic by spreadsheet formula instead of array udf), it does solve x2.  I don't have an explanation... I would swear experiment 3 worked fine the 1st time I ran it.

=====================================
(2B)+(2B)'  ?

If I re-examine experiment 1 (with initial conditions 5 0 6 0), I see that if I turn "assume non-negative" off, then solver does fine.  So I think perhaps the problem was not that partial derivative was close to zero, but that the input was stuck against a constraint (x>0) and perhaps it was inclined to move in the negative direction (which also minimizes sum of squares since <-x>^2 = <x>^2) but got stuck due to the constraint.

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)'  ?

So I land back where I started.

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)'  ?

One more observation from the discussion above is that experiment 3 and experiment 1 definitely acted different when given the same inputs (5 15 5 12)... even though they implement the same calculation.... one by spreadsheet formula and one by udf array formula.   But the reason for failure of experiment 3 under these initial conditions could still be traced to singularity (relative maximum) at the location of constraint (x=0)... and vulnerability which also showed up under different initial conditions for experiment 1.

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)'  ?

Thanks for the insight electricpete! I will take a look at my UDF again and let you know what I find.

My experience with using UDFs with Solver agrees with EclecticPete's:  UDFs work fine with Solver, regardless of whether Solver is invoked from Excel's pulldowns or from within VBA.  I have one particular spreadsheet that takes the "from  VBA" approach, and it has worked on every version of Excel from 97 to 2007.  (I have not yet tried it on 2010.)