For a two-variable Excel Solver example, maximize profit from chairs and tables by changing the number of each product made. Enter the profit and resource formulas, then use Data > Solver to maximize the profit cell subject to labor, wood, and nonnegative-production constraints; the example’s optimal answer is 20 chairs and 15 tables, earning $1,350.
What a two-variable Solver model means
In this example, the two decision variables are the number of chairs and tables to produce. Solver changes those two quantities to find the best result. The objective is to maximize total profit, while the constraints represent limited resources: at most 100 labor hours and 90 units of wood.
A model with two variables does not have to have only two constraints. It can include several resource limits, minimum requirements, or other rules. The important distinction is that Solver is changing two cells. Those cells are not the same thing as the objective cell, which calculates the result to optimize, or the constraint cells, which calculate whether the proposed solution is feasible.
This small product-mix problem is useful because its calculations are linear: each additional chair or table adds a fixed amount of profit and uses a fixed amount of each resource. That makes Simplex LP an appropriate solving method.
Set up the example worksheet
Create the following layout in a blank worksheet. The values are illustrative units, not estimates for a real business. Put the product names in B1 and C1, with Chairs in B1 and Tables in C1.
- A2: Units to make; B2 and C2: starting values, such as 0.
- A3: Profit per unit; enter 30 in B3 and 50 in C3.
- A4: Labor hours per unit; enter 2 in B4 and 4 in C4.
- A5: Wood units per product; enter 3 in B5 and 2 in C5.
- A6: Total profit; calculate the objective in B6.
- A7: Labor used; calculate total labor in B7.
- A8: Wood used; calculate total wood in B8.
With the product quantities in B2:C2, profit per unit in B3:C3, and the objective in B6, enter this formula in B6:
=SUMPRODUCT(B2:C2,B3:C3)
With quantities in B2:C2 and labor hours per product in B4:C4, enter this formula in B7:
=SUMPRODUCT(B2:C2,B4:C4)
With quantities in B2:C2 and wood use per product in B5:C5, enter this formula in B8:
=SUMPRODUCT(B2:C2,B5:C5)
SUMPRODUCT multiplies corresponding entries in the two ranges and adds the products. For instance, if B2 is 20 and C2 is 15, the profit calculation is 20 times 30 plus 15 times 50, or $1,350. Labor use is 20 times 2 plus 15 times 4, or 100 hours. Wood use is 20 times 3 plus 15 times 2, or 90 units. The units and resource totals therefore match the stated limits exactly.
Enable Solver in Excel for Windows
Solver is an Excel add-in. In Microsoft Excel for Microsoft 365 on Windows, enable it if it is not already available:
- Select File > Options.
- Choose Add-ins.
- At the bottom, beside Manage, select Excel Add-ins, then select Go.
- Check Solver Add-in and select OK. If Excel prompts you to install or enable the add-in, follow the prompt.
- Open the Data tab and look for Solver, usually in the Analysis group.
On Excel for Mac, Solver is also an add-in, but the enablement route differs: choose Tools > Excel Add-ins, select Solver Add-in, and confirm. Then look for Solver on the Data tab. Solver is not available as this desktop add-in in Excel for the web, so open the workbook in the desktop application to use these steps.
Enter the Solver settings
Choose Data > Solver to open the Solver Parameters dialog. Configure the model as follows:
- Set Objective: select B6, the total-profit cell.
- To: choose Max, because the goal is to maximize profit.
- By Changing Variable Cells: select B2:C2, the chair and table quantities.
- Add a constraint requiring B7, labor used, to be less than or equal to 100.
- Add a constraint requiring B8, wood used, to be less than or equal to 90.
- Use the nonnegative-variable option so Solver cannot choose negative production quantities.
- For Select a Solving Method, choose Simplex LP.
To add each resource constraint, select Add in Solver, choose the cell reference, select the less-than-or-equal-to relationship, and enter the limit. If the nonnegative option is not selected, add explicit constraints requiring B2 and C2 to be greater than or equal to zero. Do not put the resource limits into the changing-cell range: Solver needs to adjust B2:C2, while B7:B8 calculate resource use from those decisions.
Simplex LP fits because the objective and resource formulas are linear sums of fixed per-unit values multiplied by the changing quantities. If the model instead includes nonlinear calculations, such as a changing rate that depends on production, another solving method may be more suitable. Choosing a method that matches the model matters; a solver can only optimize the relationships represented in the worksheet.
Solve and verify the result
Select Solve. When Solver reports a solution, choose Keep Solver Solution if you want the returned quantities to remain in the worksheet. The expected result for this example is B2 = 20 chairs and C2 = 15 tables. B6 should show $1,350, B7 should show 100 labor hours, and B8 should show 90 wood units.
Those values are feasible because neither resource limit is exceeded. They are optimal because the two resource limits intersect at those quantities, and both products have positive profit. More formally, the labor limit is 2x + 4y less than or equal to 100, and the wood limit is 3x + 2y less than or equal to 90. At the intersection, both equalities hold: 2x + 4y = 100 and 3x + 2y = 90. Solving them gives x = 20 and y = 15.
It is good practice to inspect the returned values and formulas rather than relying only on the dialog message. Confirm that the changing cells contain plausible quantities, the objective reflects the intended profit, and every constraint cell stays within its limit. If Solver finds an answer that seems surprising, the issue may be an incorrectly selected cell, a missing constraint, or a formula that does not represent the actual problem.
Whole-number quantities and other constraints
The basic model permits Solver to use fractional quantities, because production variables are continuous unless you specify otherwise. This example happens to produce whole numbers, but a different set of profits or limits could produce, for example, 20.5 chairs. If products must be made in whole units, add an integer constraint for B2:C2 in Solver. Select the changing cells, add a constraint, and choose the integer relation offered in the constraint list.
Integer restrictions can make a model harder to solve, so use them only when the real decision requires whole units. Also distinguish between integer and binary decisions. An integer quantity can be 0, 1, 2, and so on; a binary quantity is limited to 0 or 1 and is useful for yes-or-no choices such as whether to open a facility. Solver supports such constraints in its desktop add-in.
Other practical rules can be added in the same way. For example, to require at least 10 chairs, constrain B2 to be greater than or equal to 10. To limit tables to 25, constrain C2 to be less than or equal to 25. The objective and constraint formulas must still calculate the consequences of the changing cells correctly.
Common problems and troubleshooting
- Solver is missing: Enable the Solver Add-in using the Excel Add-ins settings. If you are in a browser, open the file in desktop Excel instead.
- The solution exceeds a resource limit: Check that the constraint references the calculated usage cell, such as B7, rather than a per-unit input or the decision cell.
- The answer is negative or fractional: Apply nonnegative restrictions and, when appropriate, integer constraints. These requirements are not implied just because a quantity represents a product.
- Solver says it cannot find a feasible solution: Check for conflicting rules. For example, minimum production requirements may demand more labor or wood than the available limits allow.
- The objective does not change: Verify that B6 contains a formula linked to B2:C2, and that B2:C2 are the cells listed under By Changing Variable Cells.
- The result looks wrong: Check units, formula ranges, and whether the model includes all relevant constraints. A mathematically optimal answer can still be a poor real-world recommendation if the worksheet leaves out an important rule.
Solver compared with Goal Seek and alternatives
Goal Seek is designed to find a value for one input that makes one formula reach a specified target. For example, it can find the sales quantity needed for a particular profit, when only one changing input is involved. Solver is the better fit when you want to maximize or minimize an objective, change multiple cells, and enforce limits such as labor and material capacity.
A Data Table can show how formula results vary across selected input values, but it does not choose an optimum subject to a set of constraints. For a two-variable model, you can also chart feasible combinations or test a grid of values manually. Those approaches can help visualize a small problem, but Solver is more practical when there are many candidate values or multiple constraints.
The desktop Solver add-in is included with Excel as an add-in and does not require purchasing a separate Solver product. Availability still depends on using an Excel desktop installation that supports the add-in and on the account or organization allowing it. Excel for the web does not provide this Solver add-in interface; alternatives include using desktop Excel or another optimization tool that supports linear programming.
Frequently Asked Questions
Can Excel Solver solve a problem with exactly two variables?
Yes. Put the two decision variables in separate cells, build formulas for the objective and constraints, and select both cells as the changing variable range. Two variables can have more than two constraints.
Which solving method should I use for this example?
Choose Simplex LP. Profit and resource use are linear in the quantities produced, which is the structure this method is intended to handle.
Why does Solver return a decimal number of products?
Solver does not automatically know that products must be whole units. Add an integer constraint to the quantity cells if fractional production is not allowed.
Can I use Solver in Excel for the web?
The desktop Solver add-in is not available in Excel for the web. Use the Windows or Mac desktop application to enable and run the add-in.
What does a Solver solution mean when both resource limits are fully used?
It means both constraints are binding at that solution: there is no unused labor or wood under the model. That does not prove the model includes every real-world restriction, so review the assumptions before applying the result.
Related
- Import Excel to Jira CSV: Sample File and Import Steps
- Excel Import Multiple CSV Files: Methods and Tips
- How to Export a CSV File in Excel Without Losing Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel: How to Consolidate Multiple Rows Into One CellIf your goal is to combine values from matching rows into one cell, put the matching key in…
- How to Add a Shared Mailbox in Outlook MobileTo add a shared mailbox in Outlook mobile, sign in to the Outlook app with your work or…
- Excel Table Absolute References: How They WorkExcel tables do not use dollar signs to make structured references absolute: a reference such as Orders[Amount] continues…
- Excel Not Letting You Merge Cells? Fixes That WorkIf Excel will not let you merge cells, first select one contiguous rectangular range, then choose Home >…
- Excel Data Modeling: How It Works and When to Use ItExcel data modeling means organizing related information into structured tables and connecting them so you can analyze the…
- Data Validation Plan in Excel: Build Reliable ChecksA data validation plan is a clear set of rules for what information a worksheet will accept, how…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.