9. Solver encountered an error value in a target or constraint cell.

This message appears when the Solver recalculates your worksheet using a new set of values for the decision variables (Changing Cells), and discovers an error value such as #VALUE!, #NUM!, #DIV/0! or #NAME? in the cell calculat­ing the objective (Set Cell) or one of the constraints.  Inspecting the worksheet for error values like these will usually indicate the source of the problem. If you’ve entered formulas for the right hand sides of certain constraints, the error might have occurred in one of these formulas rather than in a cell on the worksheet.  For this and other reasons, it’s better to use only constants and cell references on the right hand sides of constraints.

If you see #VALUE!, #N/A or #NAME?, look for names or cell references to rows or columns that you have deleted.  If you see #NUM! or #DIV/0!, look for unanticipated values of the decision variables which lead to arguments outside the domains of your functions – such as a negative value supplied to SQRT. You can often add constraints to avoid such domain errors; if you have trouble with a constraint such as $A$1 >= 0, try a constraint such as $A$1 >= 0.0001 instead.

In the Analytic Solver products, when the Polymorphic Spreadsheet Interpreter is used (Solve With = PSI Interpreter), a more specific message usually appears instead of “Solver encountered an error value in a (nonspecific) target or constraint cell.”  At a minimum, the message will say “Excel error value returned at cell address,” where address (e.g. Sheet1!$A$1) tells you exactly where the error was encountered. Other messages may tell you more about the error. The general form of the message is:

Error condition at cell address.  Edit your formulas, or use Excel Interpreter in the Solver Model dialog.  Error condition is one of the following:

See also result code 21, “Solver encountered an error computing derivatives,” and result code 12, with messages that can appear when the Interpreter first analyzes the formulas in your model (when you click the Check Model or Solve button).

“Floating point overflow” indicates that the computed value is too large to represent with computer arithmetic; “String overflow” indicates that a string is too long to be stored in a cell.  “Division by zero” would yield #DIV/0! on the worksheet, and “Unfeasible argument” means that an argument is outside the domain of a function, such as =SQRT(A1) where A1 is negative.

“Unknown function” appears for functions whose names are not recognized by the Interpreter, such as user-written functions in VBA.  “Unsupported Excel function” appears for the few functions that the Interpreter recognizes but does not support.  The PSI Interpreter does not support the following functions:  Call(), Cell(), CubeX(), EuroConvert(), GetPivotData(), HyperLink(), Info(), RegisterID(), and SqlRequest(). 

The Evolutionary Solver and the field-installable OptQuest Solver rarely, if ever, display this message – since they maintain a population of candidate solutions and can generate more candidates without relying on derivatives, they can simply discard trial solutions that result in error values in the objective or the constraints.  If you have a model that frequently yields error values for trial solutions generated by the Solver, and you are unable to correct or avoid these error values by altering your formulas or by imposing additional constraints, you can still use the Evolutionary Solver or OptQuest Solver to find (or make progress towards) a “good” solution.