Excel SOLVER problem, claims it is solved but retains original values. Example given.

[ad9051]

Hi guys many thanks for taking the time to look at my problem

So im having a problem with using the solver add on or algorythm. I have my chain of formulae(ki:kj) I have my 5 variables(KL9:KL13) and I have a target cell to maximize(KN18) . When I run the solver it always returns the same values as was originally input, however as a quick test to see if the solver has achieved the optimum variables i simply change KL9 to 0.26. This increases my target cell, thus i assume the solver isnt working correctly at all. Also to note, these original variables were calulated by me, not originally generated by the solver. How do i get the solver to work for this example? Is it because the calulation time on the Target cell is too high (looks at 50,000 rows of data)? Ultimately i dont care if it takes solver a day to solve this problem, but i do want to achieve the correct numbers.


Because of the original size of this document it is 15mb, obviously i couldn't upload to this website as a result of this but i have provided a dropbox download link. I hope this is acceptable.

https://www.dropbox.com/s/rpd4zqyo8n...20example.xlsx

If anyone can help i would be much appreciative.

Thanks again, Alan

[Alf]

You are using the COUNTIF function and to quote Dana DeLouis

In a nutshell, Solver can not work with CountIf and If functions in the
model. This is because these are discontinuous functions. Solver is
not capable of determining a "finite difference" to help with a
derivative. Solver will often give up without warning at the first sign
of confusion.
Your model would have to be reworked without these functions.

Alf

[ad9051]

Hi Alf many thanks for your answer, i have spent so much time trying to get solver working. Any ideas how i could get around these countifs and ifs i use so regually? I do not know which functions could replace them!?

Thanks again

Alan

[Alf]

Must admit that functions like “COUNTIF” paired with “INDEX” are really not my cup of tea. Perhaps you could use "SUMPRODUCT" instead.

If I were you I would probably mark this post as solved as you now know what the solver problem is and start a new tread with title like "Replacing Countif with Sumproduct". Upload a small file as an example of what you wish to achieve and hopefully the forum gurus can help you.

Looking at your Solver set up you I see you have not set any constraints so in your present setup solver will maximize cell KM18 and minimize cell KL18 in order to maximize cell KN18.

Alf

[MrShorty]

I'm not sure replacing countif with sumproduct is really going to help in this case, because ultimately your objective function is the ratio of two counts, which will always be integers no matter what Excel function you use to generate those counts.

To illustrate what Alf is describing, I looked just at KL9. The objective function stayed constant for all values from .25534 to .25807. To something using the Newton Raphson method (like Solver), this means a slope of 0, which is the criteria used to determine if you are at the maximum.

If you must use Excel's Solver, I think you are going to have to completely rework your objective function. You'll excuse me if I'm too lazy to go through that large spreadsheet to try to determine exactly what you are trying to optimize. You'll need that objective function to be a smooth function rather than a "step" function like you currently have. At first glance, I don't see an appropriate alternative function.

If you are up to the programming challenge, another possible solution would be to abandon Solver and write your own optimization routine. Again focusing solely on KL9, I hand entered several values (.26,.29,.3,.35) and could quickly see that there was a maximum somewhere near .3. So I used a bisection type algorithm to split the .29 to .3 interval in half (.295), compare the objective function to the surrounding points, then split the interval that appears to contain the max in half again, and so on until I felt I had found the maximum (appears to be when KL9 is between .2903 and .2914, with all other inputs the same as given). Writing code for extending this to several input variables would be a significant undertaking. If you can't see a better objective function, though, this might be a valid approach.

[ad9051]

Hey all, i have contacted frontline the solver developers and have got excel solver working for me, I have come on here to say for functions like this (non smooth) it needs to be set to evolutionary, also to achieve great results for large complex problems set the amount of time it takes for the macro to conclude it is optimum to much larger! Does the business, solver has been frustrating me for such a long time, but now it is working wonders for my models.

Many thanks

Alan

[Alf]

Hi Alan

Interesting feedback on how your problem was solved.

it needs to be set to evolutionary

I assume that this setting is only available in Excel 2010?

Alf