How to Solve for a Variable in Excel & Google Sheets

This tutorial demonstrates how to solve for a variable in Excel and Google Sheets.

Solver is an add-in that is provided with Excel and used to perform “what-if” analyses by providing alternative answers to a formula in a cell based on values you pass to the formula from other cells in your workbook.

Enable the Solver Add-in

1. In the Ribbon, select File > Options.

 

 

2. Then select Add-ins and, making sure Excel Add-ins are selected in the drop-down list, and select Go…

 

 

OR
In the Ribbon, select Developer > Add-ins > Excel Add-ins.

 

 

Note: If the Developer tab is not visible in the Ribbon, you’ll need to add it first.

3. In the Add-ins dialog box that is shown, make sure that Solver Add-in is selected and click OK.

 

 

The Solver add-in now appears in the Data tab in the Ribbon.

Create a Solver Example

Set a Cell to a Specific Value

Consider the following worksheet:

 

In the sheet above, you need to break-even in Month Number 1 by setting cell B14 to 0 by amending the criteria in cells F1 to F6.

1. In the Ribbon, select Data > Analyze > Solver.

 

 

2. In the Set Objective box, ensure you have the target cell (in this case B14) selected. Set this to a value of 0. You then need to select the criteria cells in the By Changing Variable Cells box. Click in this box, and then select the cells that contain the variables (e.g., F2:F6).

 

 

3. You now need to add constraints (restrictions) to the variables.
4. Click the Add button, and then set a constraint for the solver – for example, cell F3 cannot be less than 8.

 

 

5. Click Add to add the constraint and then continue to add any further constraints that are required. Once they are all added, click OK.
6. Leave the check on Make Unconstrained Variables Non-Negative to ensure that the variable cells that do not contain constraints are not set to a negative value, and make sure GRG NonLinear is selected in the Select a Solving Method drop-down box.
7. Click Solve to solve the problem.

 

 

8. You can either now keep the solver solution or restore the original values. If you want to keep the solution, but not show the solution on the screen, you can save the solution as a scenario.

 

 

9. Click Save Scenario, and then type in the Scenario Name.

 

 

10. Click OK, and then select either Keep Solver Solution (to keep the adjusted variables) or Restore Original Values (to return the variables to their original values).

The new scenario is available in the Scenario Manager which can be access in the Ribbon by selection Data > What-If Analysis > Scenario Manager.

Get the Maximum Value in a Cell

Consider the following worksheet:

 

You wish to make as much profit as possible in selling your fruit. However, you only have 15 bananas and 6 mangoes available, and you need to sell a minimum of 15 apples, lemons, and oranges. In total, you may only sell 100 pieces of fruit.

1. In the Solver Parameters, you can select E8 as the Set Objective cell (where the profit is shown). You now set the To to Max, and then select C3: C7 as the variable cells.
2. The following criteria can then be applied:

• The quantity of bananas (C3) cannot exceed 15.
• The quantities of apples (C4), lemons (C5), and oranges (C6) must each be at least 15.
• The quantity of mangoes (C7) cannot exceed 6.
• The total quantity of fruit (C8) is 100.

 

 

3. Click Solve.

 

 

Since oranges have the highest unit price, Solver raises the number of oranges you need to sell to maximize your profits within the constraints you set.

Solve for a Variable in Google Sheets

Google Sheets allows you only to solve for a single variable and not the multiple variables that Excel allows with the Solver add-in. To do this, you need to install the Goal Seek add-in into Google Sheets. If you don’t already have this add-on installed, see: How to Add Goal Seek to Google Sheets.

Once you have the add-on installed, you can solve for a single variable. To learn how to do this, see How to Automatically Vary the Contents of a Cell in Excel and Google Sheets.