Excel2007 Solver fails to solve apparently easy problem

**Itsatwap** · 09-04-2011, 02:22 PM

Hi everyone. I'm trying to use Solver to optimise a project finance model, and I started off by using a basic example to get my head around it. Except that Solver failed to produce the best or second best solution. I was hoping that someone could tell me if I did something wrong, or else explain why the Solver algorithm fell down here.

I gave 4 power plant types a value for power generation, cost and carbon emissions (A1:D5). I set a lower limit for total power generation (L3) and an upper limit for emissions (L2), and then used Solver to optimize the numbers of each type of power plant (E2:E5) to produce the minimum cost (G6 - see below).

\1

But that produced this output, which is obviously not the best:

\1

But, when I fixed E2 at zero and eliminated it from the goalseek, Solver did achieve the right answer easily, despite the fact that it had already deduced that E2 was 0 last time:

\1

I'd appreciate any ideas.

**martindwilson** · 09-04-2011, 02:27 PM

afaik solver finds A solution not necesarilly the best

**Itsatwap** · 09-04-2011, 02:30 PM

Thanks, and I accept that, but I was hoping for some insight into whether the settings I was using for Solver were inappropriate to the task, or if not then why the algorithm failed to deal with such a simplistic problem.

**Alf** · 09-05-2011, 02:24 AM

There is an old saying "To ask the right question you must know at least 90 % of the right answer".

Well Solver does work in that way so setting up the "right" constraints can be rather difficult.

In your example there the only restraint on "Emission" was it should be <=12. I.e. a high limit. If you introduce a low limit as well on emission then Solver would be forced to take in other kinds of plants.

I've added an extra column in your example with emission cost assuming that gas "emission" costs less than oil "emission" and oil "emission" cost less then nuclear "emission".

As shown in your "Answer Report" the "Tot Emission" is not binding not really sure if that has caused the problem.

I'll play around with this a bit more and see if I can come up with a better explanation.

Alf
.

**Itsatwap** · 09-05-2011, 04:13 PM

Thanks very much for the response. Putting in the lower limit is a good point, the correct solution was reached even when I specified that emissions must be greater than one.

The point about non-binding variables: I had assumed that that was because I put in a "less than or equal to" rather than an "equal to".

**MarvinP** · 09-05-2011, 05:13 PM

Hi Itsatwap,

I know the Solver has changed from the 2007 to 2010 versions of Excel. I wonder if the newer version would suit your needs better. Read:
http://office.microsoft.com/en-us/ex...#_Toc274394092 and
http://www.solver.com/excel2010/solverhelp.htm

**Alf** · 09-06-2011, 01:27 AM

Have not found any good explanation to why Solver doesn’t find the best solution without a constraint on lower limit for emission.

Still one can always test the Solver solution to see if it is a "true" value. In you example the optimal Solution according to Solver was 400 (Cell G6).

If you add the constraint G6 <= 399 Solver will then change cell G6 value to 391 showing that the first result was not the best solution.

Alf

**Itsatwap** · 09-12-2011, 12:29 PM

Thanks for the help guys. I'm pretty sure that the evolutionary solver in 2010 would do a much better job, unfortunately the client has only just upgraded to 2007, so that'll be a hard sell.

Alf, that's a great idea, thanks mate, I'll try looping with the last solution minus a bit as a constraint.

Edit: I won't tag this one as answered just yet in case anyone else can chip in.