See all How-To Articles

How to Solve for a Variable in Excel & Google Sheets

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

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

Enabling the Solver Add-in

In the Ribbon, select File > Options.

vba solver options

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

ActivateSolver ExcelOptions

 

OR

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

ActivateSolver Ribbon Developer

NOTE: If the Developer tab is not visible in the Ribbon, click here to learn how to show the Developer tab in the Ribbon.

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

ActivateSolver Solver

 

The Solver Add-in will now appear in the Data tab in the ribbon.

Creating a Solver Example

Setting a cell to a specific value

Consider the following worksheet:

vba solver sheet

In the sheet above, we need to break even in Month Number one by setting cell B14 to zero by amending the criteria in cells F1 to F6.

In the Ribbon, select Data > Analyze > Solver.

ActivateSolver Solver Analyze

In the 1) Set Objective box, ensure that you have the target cell (in this case B14) selected.   We wish to set this to a 2) value of 0. We then need to 3) select the criteria cells in the “By Changing Variable Cells” box.  Click in this box, and then select the cells that contain the variables (eg: F2:F6).

Solver Set Cells

We now need to add contraints (restrictions) to the variables.

Click the Add button, and then set a contraint for the solver – for example, cell F3 cannot be less than 8.

Solver AddConstraint

Click Add to add the constraint and then continue to add any further constraints that are required.  Once they are all added, click OK.

Solver Constraints

Leave the check on “Make Unconstrained Variables Non-Negative” in order 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.

Click Solve to solve the problem!

Solver Solution

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.

Click Save Scenario, and then type in the Scenario name.

Solver Scenario

Click OK, and then either select Keep Solver Solution to keep the adjusted variables, or Restore Original Values to return the variables to your original values.   The new scenario will be available in the Scenario Manager which can be acccess in the Ribbon by selection Data > What-If Analysis > Scenario Manager.

Getting the maximum value into a cell

Consider the following worksheet:

Solver Fruit

We wish to make as much profit as possible in selling our fruit.  However, we only have 15 Bananas and 6 Mangoes available, and we need to sell a minimum of 15 Apples, Lemons and Oranges.  In total, we may only sell 100 piece of fruit.

In the Solver Parameters, we can select E8 as the Set Objective cell (where the profit will be shown).  We now set the To to Max, and then select C3: C7 as the variable cells.

The following criteria can then be applied:

Solver Max

Click Solve to solve the problem.

Solver Solve Max

Due to the fact that Oranges have the highest unit price, the Solver  raises the amount of oranges that we need to sell to maximize our profits within the contraints that we have set!

How to Solve for a Variable in Google Sheets

Google sheets allows us only to solve for a single variable and not the multiple variables that Excel allows with the Solver Add-in.  In order to do this, we 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-in installed, you are able to solve for a single variable.  To learn how to do this, see How to Automatically Vary the Contents of a Cell in Excel & Google Sheets.