Excel Solver’s Simplex LP method finds the best value for a linear objective while respecting linear constraints. Choose it in the Solver Parameters window when your decision variables, objective formula, and constraints are all linear; for a small production-planning example, it can find the profit-maximizing quantities that satisfy limits on labor and materials.
What Simplex LP means in Excel
Simplex LP is Excel Solver’s method for solving linear programming problems. LP stands for linear programming: you choose values for decision variables to maximize or minimize a target, subject to limits. Examples include maximizing profit, minimizing shipping cost, or allocating a fixed number of staff hours among tasks.
In a linear model, each decision variable appears by itself, multiplied by a constant. The variables can be added together, but they are not multiplied by one another, raised to powers, or used inside nonlinear functions. Both the objective and each constraint must have this linear structure for a standard Simplex LP model.
Solver is an Excel add-in, rather than an ordinary worksheet function. On Excel for Microsoft 365 for Windows, enable it through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Solver Add-in, and select OK. Then open Data > Solver. In Excel for Mac, enable the add-in through Tools > Excel Add-ins; Solver is then available from the Data tab. The standard Solver add-in is not available in Excel for the web.
How Solver uses a linear model
A linear programming model has three essential parts:
- Decision variables: the cells Solver is allowed to change, such as the number of units to produce.
- Objective: a formula in one cell that Solver will maximize, minimize, or set to a specified value.
- Constraints: limits or requirements expressed as relationships between cells, such as resource use being less than or equal to available capacity.
Conceptually, the Simplex method searches the feasible region: the set of variable values that satisfy every constraint. For a linear model with a finite optimum, a best solution can be found at a corner, or vertex, of that region. Solver uses the model’s coefficients and constraints to search efficiently rather than trying every possible combination of values.
That does not mean every spreadsheet model is linear just because it has a target and some limits. A formula such as a constant times a changing cell is linear. A formula that multiplies two changing cells, divides by a changing cell, or uses a changing cell in a power is generally nonlinear. The distinction determines whether Simplex LP is suitable.
Worked example: maximize production profit
Suppose a workshop makes products X and Y. Each unit of X earns $30 and uses two hours of labor and one unit of material. Each unit of Y earns $20 and uses one hour of labor and two units of material. The workshop has at most 100 labor hours and 80 material units. Let the production quantities be nonnegative. The model is to maximize total profit while staying within both resource limits.
Arrange the worksheet with product names in A2:A3, quantities in B2:B3, and profit per unit in C2:C3. Put 30 and 20 in C2:C3, and reserve B2:B3 as the changing cells. With the quantities in B2:B3 and unit profits in C2:C3, enter this objective formula in D4; at the optimum it returns 1600, meaning $1,600 of profit:
=SUMPRODUCT(B2:B3,C2:C3)
With B2 holding X’s quantity and B3 holding Y’s quantity, put this formula in E2 to calculate labor used; at the optimum it returns 100:
=2*B2+B3
With the same quantity cells, put this formula in E3 to calculate material used; at the optimum it returns 80:
=B2+2*B3
In Solver, set the objective to D4 and choose Max. Set By Changing Variable Cells to B2:B3. Add E2 less than or equal to 100 and E3 less than or equal to 80. Ensure B2 and B3 are nonnegative, either by adding those constraints or selecting Make Unconstrained Variables Non-Negative in Solver Options. Choose Simplex LP as the solving method, then select Solve.
The solution is 40 units of X and 20 units of Y. Those quantities use 2 × 40 + 20 = 100 labor hours and 40 + 2 × 20 = 80 material units. Profit is 30 × 40 + 20 × 20 = $1,600. The result is better than producing only X, which earns $1,500 at the labor limit, or only Y, which earns $800 at the material limit. The example also shows why the objective formula and constraints need to reflect the real problem accurately: Solver optimizes the model it is given, not the situation outside the worksheet.
How to choose the right solving method
In the Solver Parameters window, select a method from the solving-method list. Use the structure of the formulas to decide:
- Simplex LP: use it for a linear objective and linear constraints. It is generally the right choice for product mix, resource allocation, and many transportation or scheduling models when their formulas are linear.
- GRG Nonlinear: consider it when the model is smooth but nonlinear, such as one with changing variables multiplied together or certain nonlinear cost relationships.
- Evolutionary: consider it for discontinuous or highly irregular models that are difficult for the other methods. It may take longer and its results can depend on the search settings.
These methods are not interchangeable labels for the same calculation. For example, switching from Simplex LP to GRG does not fix a badly designed model, and selecting Simplex LP does not make nonlinear formulas linear. If an objective includes a formula such as a decision variable multiplied by another decision variable, first consider whether the problem can be reformulated as a linear model. If not, choose a suitable nonlinear method and interpret its results accordingly.
Integer decisions and other Solver options
Some decisions must be whole numbers: a business cannot make 3.6 machines, and a yes-or-no choice is naturally represented by a binary value. Solver lets you add integer or binary constraints to changing cells. Those restrictions can make a linear model a mixed-integer problem. Solver can handle supported integer constraints, but the search may be substantially harder than solving the continuous version, so it can take longer or be more sensitive to the model’s size and settings.
Do not assume that Simplex LP means every variable must be allowed to take decimals. It can be used with a linear model that has integer restrictions, but the integer requirements change the optimization task. For example, a continuous model might recommend 3.5 units, while an integer-constrained model must choose a whole number and may return a different objective value.
Other Solver options can affect how the model is interpreted or searched. For a linear model, review the linear-model option if it is shown, and check the nonnegative-variable setting rather than assuming negative values are prohibited. Negative quantities may be mathematically allowed unless the model rules them out; that can produce a result that is valid to Solver but impossible in practice.
Common setup problems and how to diagnose them
A Solver result is only as useful as the worksheet model. If the result is unexpected, inspect the formulas and constraints before changing the solving method. Common issues include:
- A missing constraint: omitting a labor, budget, or capacity limit can let Solver recommend an unrealistic amount.
- An incorrect inequality: a requirement to produce at least a minimum amount uses greater than or equal to, not less than or equal to.
- Wrong changing cells: Solver may be adjusting formula cells or unrelated inputs instead of the decision variables.
- Variables allowed to go negative: add nonnegative constraints when negative values have no real-world meaning.
- Nonlinear formulas in a linear model: inspect the objective and constraint calculations for products of changing cells, divisions by changing cells, or other nonlinear operations.
- Infeasible constraints: the requirements may conflict. For instance, a minimum production target may require more labor than the available capacity allows.
When Solver reports that it cannot find a feasible solution, check whether any solution can satisfy all constraints at once. When it reports an unbounded solution, look for a missing limit that would otherwise cap improvement in the objective. A successful solve does not prove the assumptions are sensible: check the returned values against units, business rules, and any omitted real-world restrictions.
Interpreting results and reports
After Solver finishes, review the result message and choose whether to keep the solution or restore the original values. Keeping the solution writes the selected variable values into the worksheet. It does not certify that the data or assumptions are correct. Check whether the constraints are met, whether any are exactly at their limits, and whether the objective value is plausible.
For a suitable continuous linear model, Solver can offer reports such as an Answer Report and a Sensitivity Report after a successful solve. The Answer Report summarizes the objective, changing cells, and constraints. The Sensitivity Report can provide information about how changes to coefficients or constraint limits relate to the optimum. It is not generally available for models with integer constraints, and it should not be treated as a forecast that remains valid for arbitrarily large changes. Its interpretation depends on the model and the conditions around the reported solution.
If a problem has many decision variables, complicated rules, or a need to run repeatedly as part of a larger process, consider whether worksheet Solver is an appropriate tool. Excel’s built-in add-in is convenient for small and moderate models, but it is not a substitute for specialized optimization software when a project’s scale or requirements exceed its capabilities.
Frequently Asked Questions
Is Simplex LP the same as Solver?
No. Solver is Excel’s optimization add-in, and Simplex LP is one of its available solving methods. Solver also offers GRG Nonlinear and Evolutionary methods for other kinds of models.
When should I choose Simplex LP in Excel Solver?
Choose it when the objective and constraints are linear in the changing cells. It is a good fit for models such as maximizing profit from products with fixed per-unit returns and limited resources.
Can Simplex LP handle integer or binary variables?
Solver supports integer and binary constraints in linear models, but these restrictions make the problem harder than a continuous linear model. Integer solutions can take longer to find, and some reports, including the Sensitivity Report, are unavailable for integer-constrained models.
Why does Solver say my model is not linear?
Look for formulas that multiply changing cells together, divide by a changing cell, or otherwise make the objective or constraints nonlinear. A changing cell multiplied by a fixed constant is linear; multiplying two changing cells is not.
Can I use Excel Solver in a browser?
The standard Solver add-in described here is for the Excel desktop application, not Excel for the web. Use Excel for Microsoft 365 on Windows or the desktop Mac version to access the built-in add-in.
Is the Simplex LP result always unique?
No. A linear model can have more than one solution with the same optimal objective value. Solver may return one of those optima; if the model needs a particular tie-break, represent that preference explicitly with an appropriate additional constraint or objective.
Related
- Excel Scatter Plot: How to Swap X and Y Axes
- Excel Scatter Plot Regression Line: Add and Interpret It
- Excel Scatter Plot Not Showing All Data? Try These Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel File Merge Online Free: Safe Ways to Combine WorkbooksYes, you can merge Excel files online for free, but the best method depends on what you mean…
- Excel Quadratic Solver: Find Equation Roots Step by StepExcel has no dedicated quadratic-equation function, but you can solve a quadratic directly with cell formulas. For an…
- Google Sheets Dropdown From Range: Setup, Options & FixesYes. In Google Sheets, select the cells that need a dropdown, choose Data > Data validation > Add…
- Extract Data from Google Maps to Excel: Practical OptionsYou can move location data into Excel, but Google Maps does not provide a general-purpose button to export…
- How to Insert an Excel File in Word: 3 Easy MethodsTo insert an Excel file in Word, use Insert > Object > Object > Create from File to…
- How to Change Zoom in Outlook Email View on Desktop and WebTo change the zoom in an Outlook email, first identify which Outlook you use. In classic Outlook for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.