Solver clash with custom Excel function

[yngeng33]

Hi everyone,

I'm encountering an issue with Excel Solver giving me an incorrect solution to an optimization problem.

My objective function is f() = [a] - ; where both a and b are a function of a third variable - time (specifically, number of years). My constraint on f() is that it is >= 0. Time is the variable I am changing in my optimization, and it is constrained to a positive (including 0) integer value.

Further, in my spreadsheet, the value of [a] comes from a user-defined function I've written in VBA that uses a For loop which takes the time variable I'm changing in the optimization as the loop termination point. (e.g. the loop looks like "For i to (no. of years)").

The problems I encounter are:

a) whenever the starting value of f() is negative before I run the Solver optimization; once I run solver to optimize, I get the message "Solver could not find feasible solution"

b) even if Solver finds a solution, it is generally suboptimal - i.e. I can usually manually enter a time that minimizes the [a]- differential even further

c) when it does fail to find a solution, I end up with a decimal value in the time field, even though I've clearly constrained it to an integer value

I've tested the optimization problem with [a] being derived from a different in-built Excel function; as opposed to my one which uses a 'For' loop. It works perfectly, so either there is something wrong with my function (I've checked it a dozen times and it works fine) or there is some clash with Solver I'm not aware of.

My question is - is there some reason the solver iterations/guesses of the changing variable (i.e. time, in my case) can clash with the for loop in my custom function? If I constrain the changing variable as being an integer (as I've done), will Solver only guess integer values for it and if so, why is this clashing with my function?

Any help/suggestions are greatly appreciated

 

[cummings54]

try adding the

Application.Volatile

statement to you user defined function.

 

[Denial]

A shotgun blast of minor comments:

I have used SOLVER with a user-defined function, and everything worked fine. So I do not think that there is any fundamental incompatibility between SOLVER and UDFs.

Are you trying to minimise f(), or ABS(f())? Your wording about trying to minimise the "[a]- differential" suggests the latter possibility. If the latter, try minimising ([a]-)^2 instead, as it is a smoother function in the vicinity of zero.

I would be surprised if Cummings54's application.volatile suggestion makes any difference, but it is still worth trying. (If it does NOT work, do not leave that statement in: it will slow things down.)

Do you get the sort of behaviour you are expecting if you remove the constraint that time has to be a non-negative integer?

Check the settings in the Solver/Options box. Try activating automatic scaling. (It is unlikely to help, but you won't lose much time giving it a try.)

Solver will only find a local optimum, so your comment (b) does not necessarily surprise me. What does surprise me is that you seem to be implying that this behaviour goes away when you use your non-UDF workaround.

The normal approach to this local-optimum-only problem is to make sure you start "close to" the optimum you want, something that is not always easy to do. (The authors of Solver will sell you an advanced version that they claim is better at seeking out solutions a longer way from your starting point.)

Live with the workaround you have found??

 

[IRstuff]

Have you considered using abs(a(t)-b(t))?

Perhaps you can post the actual sheet?

TTFN

FAQ731-376

 

[yngeng33]

Hi again,

Thanks everyone for the tips. I tried everything that has been suggested and the problem still persists. In response to a question above, I actually need to minimise [a]- subject to the answer being positive; so absolute value isn't going to help (nonetheless, I tried it and changed my constraints slightly - didn't help).

I'm attaching a stripped-down version of my sheet here that illustrates the problem. Data highlighted in blue is manual input (what is meant to be changed). The cell that carries my UDF that I think is causing the problem is highlighted in red.

By playing with some of the numbers, (e.g. increasing the 'cost of asset' figure to make the objective function initially negative) and running the Solver optimization via the macro I've coded in the button; you'll be able to see the problems I've described earlier.

In any case, getting this to work with my UDF isn't crucial. Now I'm just trying to understand the limitations of Solver and where it is best used, and where it should be avoided.

Again, any help/pointers are greatly appreciated.

Cheers

 

[joerd]

This is not an Excel / VBA issue, but a numerical issue. Your function is not continuous, but it will give the same answer for fractions of years. In order for Solver to determine the minimum, it will try to determine the gradient at a certain value of the independent variable (time, in your case). It will, even for integer variables, play with the value and see if the gradient is zero, and then assume that that is the minimum.
Your problem can be optimized much more easily if you use the Excel FV (future value) function, which is continuous, so it gives different values for fractions of the year as well. I suggest you change the formula in J10 to [tt]=-FV($C$6*(1-$C$4),$J$10,$J$8,$C$8,0)[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.