Yes, it can work when you start with a broken constraint, provided there is a feasible solution to be found. For example, it worked for me with the problem and initial conditions you gave, but only when I unchecked the 'Make Unconstrained Variables Non-Negative' box (because there is no feasible solution to the problem you gave that does not have negative-valued variables). If your real problem is different and you're still getting 'Solver could not find a feasible solution' result, it might be instructive to relax some of your constraints (or change the objective to a Min or Max). – May 1 '13 at 14:05. You can approximate multiple objective cells by making your (single) objective some function (like a sum, sum of squares, minimum, minimum of squares, minimum of absolute values, etc.) of the cells in question. You will need to find a reasonable way of including/weighing this along with your 'real' objective.
Altenatively, you can leave or original objective unchanged and put constraints on the cells that you want to be close to zero and progressively relax these constraints if needed to find a feasible solution. – May 1 '13 at 15:02.
Excel's Solver is a numerical optimization add-in (an additional file that extends the. If you cannot find the Solver Add-In, try using the Mac's Find File or Find in. This cell must contain a formula that depends on one or more other cells. If you wish to keep a particular Solver model, then use the Options and Save Model.
. Click the Solver command to display the Solver Parameters dialog. You can resize this dialog by selecting its lower right corner and dragging. If you don't see the Solver command (on the Data tab on Windows, or the Tools menu on Macintosh), you need to. In the Excel Web App, the Solver Parameters dialog should be visible on the worksheet. If you don't see it, you need to.
In the Set Objective box, enter a cell reference or name for the objective cell. The objective cell must contain a formula.
![One One](/uploads/1/2/5/6/125605093/553973841.jpg)
Do one of the following:. If you want the value of the objective cell to be as large as possible, click Max. If you want the value of the objective cell to be as small as possible, click Min. If you want the objective cell to be a certain value, click Value Of, and then type the value in the box. You may leave the Set Objective box empty.
In this case Solver finds values for the decision variables that satisfy the constraints. Using the Value Of option has the same effect as defining a constraint (see below) where the objective cell must be equal to the specified value. In the By Changing Variable Cells box, enter a name or reference for each decision variable cell. Separate the nonadjacent references with commas. To have an effect, the variable cells must be related directly or indirectly to the objective cell, or to the cell reference in at least one constraint.
You can specify up to 200 variable cells. In the Subject to the Constraints box, enter any constraints that you want to apply. Select the Make Unconstrained Variables Non-Negative check box to specify that all decision variable cells without explicit lower bounds should be given lower bounds of zero. If you define a = constraint that specifies a lower bound different from zero for certain decision variable cells, it will take precedence over this option for those cells. Click Solve and in the Solver Results dialog box, read the message at the top and the more detailed explanation at the bottom of this dialog. After reading these messages, do one of the following:.
To keep the final values in the decision variable cells, click Keep Solver Solution. To restore the values of the decision variable cells at the time you clicked Solve, click Restore Original Values. Select the Return to Solver Parameters Dialog check box if you want to modify the Solver model or re-solve as your next step. Click OK or Cancel. If you click OK, Solver re-displays the Solver Parameters dialog if you selected the above check box; otherwise it returns to Excel worksheet mode. If you click Cancel, other selections in the Solver Results dialog box (such as Keep Solver Solution and any selected reports) are ignored; the original values of the decision variable cells are restored, and Solver returns to Excel worksheet mode. NOTES.
You can interrupt the solution process by pressing ESC. Microsoft Office Excel recalculates the worksheet with the last values that are found for the decision variable cells, and displays the Show Trial Solution dialog.
To create a report that is based on your solution after Solver finishes, you can click a report type in the Reports box and then click OK. The report is created on a new worksheet in your workbook.
If Solver doesn't find a solution, either certain diagnostic reports or no reports are available. To save your decision variable cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.