Solver Problem: Only 100 Values To Check

[danc1982]

Hi all,

Keeping this brief: I am trying to run a Solver on a problem that maximises one cell by changing only 1 other variable cell - and one that must be an integer between 1 and 100. There are therefore literally only 100 different 'examples' to run - but Solver is either taking an incredibly long time to run it.

It is quite a complicated model that it runs through (meaning the problem is not linear) but manually changing the variable (i.e. just typing in a number) takes much less than a second on hitting Enter, so my (naive) thinking was that the theoretical maximum time Excel could need to run every single one of the 100 possible solution is 100 seconds... sadly not.

Presumably Solver is running all kinds of non-integer values in an attempt to hone-in on the optimum solution, rather than looking at the restraints and realising all it has to do is try 100 different answers.

My question really is: am I missing something incredibly obvious, i.e. is there another tool other than Solver than I should be using to make Excel try values 1-100 in the variable cell to see which one returns the maximum value of the objective cell.

If necessary I can create a much-simplified version of the worksheet to upload here, that removes all confidential info - but I realise such a version may (by being smaller) not have as big a problem... I have attached a screenshot of the Solver settings in case anyone can see something obvious here:

Solver.PNG

On Solver Method: Simplex LP does not work as the problem is not Linear; GRG Non-Linear returns the wrong answer (it always says whatever value is currently in R2 is the optimum answer, even when it isn't). Evolutionary is the one method that does work......... eventually.

Any help gratefully received - many thanks in advance!

[Greg M]

Hi there,

Here's a knee-jerk response - a sort of do-it-yourself Solver which I'm unable to test without access to your workbook. Try it and see if it works for you.

Insert the following code into a standard VBA CodeModule and run it:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Alf]

Perhaps something simpler would do as well?

Please Login or Register  to view this content.

This would put value from cell F104 to range M104:M204 and a formula like "=LARGE(M10:M204,1) would give you the highest number.

Alf

[MrShorty]

I could see using a Data table for this instead of Solver: http://www.excel-easy.com/examples/data-tables.html