# How to Solve for a Variable in Excel & Google Sheets

Written by

Reviewed by

*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

- In the
**Ribbon**, select**File > Options**.

- 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.

- 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.

- In the
**Ribbon**, select**Data > Analyze > Solver**.

- 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).

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

- Click
**Add**to add the constraint and then continue to add any further constraints that are required. Once they are all added, click**OK**. - 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. - Click
**Solve**to solve the problem.

- 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**.

- 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.

- 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. - 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.

- 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.