Optimization of variables is needed to solve any equation in mathematics. Excel has some tricks for solving equations with less time consumption. The **Goal Seek** and formulas are some tricks to solve equations in Excel. But they cannot optimize the variables. **Excel Solver** is a good solution in this case. In this article, we will discuss how to optimize multiple variables using **Solver** in Excel with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**5 Steps to Optimize Multiple Variables in Excel**

In this article, we will **use the Excel Solver **to optimize multiple variables. Assume that a company produces the TV and refrigerators. Now, we will use the **Solver** to get the maximum profit by optimizing the number of TV and refrigerator production based on some constraints. The number of TV and refrigerator are the variables. We will describe all the things in the below section.

### ðŸ“Œ Step 1: Design Dataset and Input Data

- First, we design the dataset to insert all the data.

- The profit after selling each TV, and refrigerator are
**$21**and**$15**respectively. The production of TV and refrigerator has two sections: mechanical, and assembling. We input that information into the dataset.

The coefficients are the variables that we need to optimize here.

**Read More: ****Where Is Solver in Excel?**

### ðŸ“Œ Step 2: Insert Equations Using Formula

- Now, go to
**Cell E5**to calculate the maximum profit using the following formula.

`=SUMPRODUCT(C5:D5,$C$6:$D$6)`

Here, **C5**, and **D5 **indicate the profit for **1** item each. And **C6, D6** are the coefficients of TV and refrigerator. Those are the variables of this problem. We have to optimize the value of those two variables.

- Again, apply the similar
**SUMPRODUCT**formula on**Cells E9**and**E10**. Those will calculate the total production workdays.

**For Cell E9:**

`=SUMPRODUCT($C$6:$D$6,C9:D9)`

**For Cell E10:**

`=SUMPRODUCT($C$6:$D$6,C10:D10)`

- Now, put a
**SUM**formula on**Cell E6**.

**Â**

`=SUM($C$6:$D$6)`

This calculates the total production quantity. We have a restriction that the maximum production quantity will not exceed **100**.

**Read More: ****Some Practical Examples with Excel Solver**

### ðŸ“Œ Step 3: Enable Solver in Excel

- Now, we will enable the
**Solver**. - Go to
**File**>>**Options**. - Choose the
**Add-ins**option from the right section. - Click on the down arrow of the
**ManageÂ**section. - Choose the
**Excel Add-insÂ**option. - After that, click on the
**GoÂ**button.

- Mark the
**Solver Add-in**from the**Add-ins**window and press the**OKÂ**button.

- Now, go to the
**DataÂ**tab.

- We can see the
**Solver**add-in is available here. - The
**Solver Parameters**window appears.

We will set different cell references in this window.

**Read More: ****Resource Allocation in Excel (Create with Quick Steps)**

### ðŸ“Œ Step 4: Input Solver Parameters for Optimization

- Choose
**Cell E5**, which will determine the maximum profit as the objective. - Then, mark the
**MaxÂ**option. - Select
**Range C6:D6**as the variables.

There are more options to add constraints. For that click on the **Add** button.

- The
**Add Constraints**window appears. - Choose the
**Cell Reference from**the dataset. - Then, set the symbol and the value of the constraint.

If we have more than one variable, click on the add button and the constraints one by one. Finally, press the **OK** button.

- We can see all the constraints are added in the window.
- Unmark the
**Make Unconstrained Variables Non-Negative**and choose**Simplex LP**as the method. - Finally, click on the
**SolveÂ**button.

Here, we set four constraints. The sum of products equal to or less than **100**, coefficients must be greater than **0**, total workdays for the mechanical part is equal to or less than **280** and assembling is **200**.

- The
**Solver Results**window appears. - Mark the
**Keep Solver SolutionÂ**option. - Then, choose the
**Answer, Sensitivity,**and**Limits**options from the**ReportsÂ**section. - Finally, press the
**OKÂ**button.

- Look at the dataset.

We can see the coefficients are changed after optimization. There are also three reports created.

**Read More: ****Example with Excel Solver to Minimize Cost**

### ðŸ“Œ Step 5: Analyse Reports of the Solver

- Look at the
**Answer Report**.

We can see the initial value of the max profit was **0**, and the final value of **1440**. For variables, initially, that was **0,** and finally **40** for each TV and refrigerator.

- Again, look at the
**Sensitivity Report**.

We can see workdays of mechanical and assembling are the value as we set the constraints. But the total number of productions was set equal to or less than **100**, and we get that **80** after optimization.

- Finally, we have the
**Limits Report**.

The lower limit for TV and refrigerator set to **0,** and after optimization, we get this as **40** for both. If we change the constraints those values will also change similarly.

In this way, we can optimize multiple variables in **Excel** with **Solver**.

**Conclusion**

In this article, we have described how to optimize the multiple variables in Excel with Solver. If you need only solution without optimization, you can use the Goal Seek feature and customize the formula. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy**Â and give your suggestions in the comment box.