Excel Data Analysis For Dummies
Book image
Explore Book Buy On Amazon

For simple problems, the Solver in Excel usually quickly finds the optimal Solver variable values for the objective function. But, in some cases, Solver has trouble finding the Solver variable values that optimize the objective function. In these cases, Solver typically displays a message or an error message that describes or discusses the trouble that it's having with your problem.

Solver has found a solution

The message tells you that Solver has done is job and found a set of variable values that satisfy your constraints.

Solver has converged to the current solution

The message tells you that Excel has found a solution but isn't particularly confident in the solution. In essence, this message alerts you to the possibility that a better solution to your optimization modeling problem might exist. To look for a better solution, adjust the Convergence setting in the Solver Options dialog box so that Excel works at a higher level of precision.

Solver cannot improve the current solution

The message tells you that, well, Excel has calculated a rough, pretty darn accurate solution, but, again, you might be able to find a better solution. To tell Excel that it should look for a better solution, you need to increase the precision setting that Solver is using.

This means, of course, that Excel will take more time. But that extra time might result in it finding a better solution. To adjust the precision, you again use the Solver Options dialog box.

Stop chosen when maximum time limit was reached

The message tells you that Excel ran out of time. You can retry solving the optimization modeling problem with a larger Max Time setting. Note, however, that if you do see this message, you should save the work that Excel has already performed as part of the optimization modeling problem solving.

Save the work that Excel has already done by clicking the Keep Solver Results button when Excel displays this message. Excel will be closer to the final solution the next time that it starts looking for the optimal solution.

Solver stopped at user’s request

Er, obvious right? Solver good dog. Solver stopped because master told it to stop. Solver get treat.

Stop chosen when maximum iteration limit was reached

The message tells you that Excel ran out of iterations before it found the optimal solution. You can get around this problem by setting a larger iterations value in the Solver Options dialog box.

Objective Cell values do not converge

The message tells you that the objective function doesn't have an optimal value. In other words, the objective function keeps getting bigger even though the constraint formulas are satisfied. In other words, Excel finds that it keeps getting a better objective function value with every iteration, but it doesn't appear any closer to a final objective function value.

If you encounter this error, you’ve probably not correctly defined and described your optimization modeling problem. Your objective function might not make a lot of sense or might not be congruent with your constraint formulas. Or maybe one or more of your constraint formulas — or probably several of them — don't really make sense.

Solver could not find a feasible solution

The message tells you that your optimization modeling problem doesn't have an answer. As a practical matter, when you see this message, it means that your set of constraints excludes any possible answer.

For example, suppose that it takes 3,000 hours to write a book and that only 2,000 hours for work are available in a year. If you said that you wanted to write at least one book a year, there’s no solution to the objective function.

A book requires up to 3,000 hours of work, but you only have 2,000 hours in which to complete a 3,000-hour project. That's impossible, obviously. No optimal value for the objective function exists.

Linearity conditions required by this LP Solver are not satisfied

The message indicates that although you selected the Simplex LP solving method, Excel has now figured out that your model isn't actually linear. And it's mad as heck. So it shows you this message to indicate that it can't solve the problem if it has to assume that your objective function and constraint formulas are linear.

If you do see this message, go ahead and try the GRG Nonlinear solving method.

The problem is too large for Solver to handle

The message means that you’ve got a problem too large for solver either because you’ve tried to model with more than 200 decision variable or more than 100 constraints. To work around this problem, you may be able to try minimizing the number of variables or constraints so their counts fall below the “hey buddy that’s just too large” constraint.

Solver encountered an error value in a target or constraint cell

The message means that one of your formula results in an error value or that you goofed in describing or defining some constraint. To work around this problem, you need to fix the bogus formula or the goofy constraint.

There is not enough memory available to solve the problem

The message is self-descriptive. If you see this message, Solver doesn't have enough memory to solve the optimization modeling problem that you’re working on. Your only recourse is to attempt to free up memory, perhaps by closing any other open programs and any unneeded documents or workbooks.

If that doesn't work, you might also want to add more memory to your computer, especially if you’re going to commonly do optimization modeling problems. Memory is cheap.

Error in model. Please verify that all cells and constraints are valid

The message means that you’ve got something goofy — probably also something fixable — in your optimization problem. Check your formulas and your input values. Make sure there’s nothing obviously wrong. Oh, and one other thing: Make sure you’re not using the word “solver” in any of your named variables. That can confuse Solver.

About This Article

This article can be found in the category: