**Optimization analysis** is a more complex part of goal-seeking analysis for one or more variables based on certain constraints. Even you can change the constraints and control the optimization process. It may sound very complex. But **Microsoft Excel **has introduced **Solver** for making such optimization analysis easier. In this article, we will first get to **Excel Solver **a little more. Then we will describe the process of** excel optimization with constraints** through **3 **case scenarios.

**Table of Contents**hide

## Download Practice Workbook

Download this sample file to practice by yourself.

## What Is Excel Solver?

**Excel Solver** is a kind of** What-if** analysis tool that holds a special set of commands. It is used for optimization as well as a simulation in many business and engineering models.

**Excel Solver** is the best tool for optimization with constraints. It helps to determine the return on investments, optimal budget, production cost, scheduling workforce, and lots more.

## Types of Methods for Optimization in Excel Solver

There are three methods in solver for optimization. They are as follows:

**Simplex LP:**In this method, each constraint refers to a linear model requirement. Therefore the target cell is optimized in (**changing cell*constant**) form and compared to the sums of a constant.**Generalized Reduced Gradient (GRG) Nonlinear:**When target cell or constraints or both refer to changing cells, it refers to a nonlinear method for optimization.**Evolutionary:**It is quite similar to GRG nonlinear only with the difference that it is used for smooth nonlinear problems.

## 3 Case Scenarios of Optimization with Constraints in Excel

Now, let us learn the process of optimization with constraints in excel solver with the following **3** case scenarios.

### 1. Optimization with Constraints in Maximize Open Channel Flow

In this case, we will describe constrained optimization by maximizing open channel flow in a **trapezoidal cross-section**. For this, we will consider **3 variables**– **Top Width**, **Height **and **Angle **of side walls. With the help of constraints, we can optimize the flow rate by maximizing the **Hydraulic Radius **based on the variables. Here, **Hydraulic Radius **is defined when any cross-sectional area is divided by the **Wetted Perimeter **(**R=A/p**). Now, go through the following steps carefully.

- In the beginning, provide the
**Inputs**,**Values**and their**Units**in the**Cell range B5:D8**.

- Then insert this formula in
**Cell G5**to calculate the**Bottom Width**of the trapezoid channel.

`=C5-2*C6*(TAN(RADIANS(90-C7)))`

**the RADIANS function**to convert the degree angle in

**Cell C7**to radian and determine the tangent value with

**the TAN function**.

- After this, type this formula in
**Cell G6**to find out the**Cross-Sectional area**(**A**).

`=C6*(G5+C5)/2`

- Following this, calculate the value of the
**Wetted Perimeter**(**p**) with this formula in**Cell G7**.

`=G5+2*SQRT(((C5-G5)/2)^2+C6^2)`

**the SQRT function**to get the square root value.

- Lastly, get the
**Hydraulic Radius**with this formula in**Cell G8**.

`=G6/G7`

- Along with this, determine the units in the
**Cell range H5:H8**as shown below.

- Next, we will add constraints and variables in
**Excel Solver**. - For this, go to the
**Data**tab and choose**Solver**from the**Analyze**section.

- Here, insert
**Set Objective**as**Cell G8**because we will maximize the**Hydraulic Radius**for optimization.

- Then, refer to
**Cell range C5:C7**as the**By Changing Variable Cells**because these values will change through optimization.

- Now, click on
**Add**in the**Solver Parameters**window.

- Following this, insert the
**Cell Reference**and**Constraint**value as shown below. The constraint value will set up a limit for optimization.

**Note:**

**Constraint**value can either be a numeric value or a cell reference. But it is a best practice to refer to a cell.

- Then, press
**OK**and you will see the conditions in the**Subject to the Constraints**box.

- Now, click on
**Solve**and it will direct you to the**Solver Results**window. - In this window, mark checked the
**Keep Solver Solution**and click**OK**.

- Finally, the values will be optimized because the
**Target Area**and the**Cross-Sectional Area**are now equal.

- It also optimized the
**Input**values of the variable cells based on the constraint.

**Read More: ****How to Solve Network Optimization Model in Excel (3 Cases)**

### 2. Optimization with Constraints in Linear Programming Problem

In this second case, we will analyze and optimize the **Total Profit** of a manufacturing company for advertisement. For this, we will consider **2 Publishers’ Budget for Advertisement **as the variables. Follow the process below to optimize this case with constraints as a linear programming problem.

- First, determine the values of
**Cost per Unit**,**Price per Unit**and the**Advertisement Cost per Unit**for the specific product in the**Cell range B5:C6**.

- Then, specify the
**Available No. of Units**and the**Budget for Advertisement**for each publisher in the**Cell range C9:D10**.

- Now, we will calculate some parameters for
**Publisher 1**. - For this, insert this formula in
**Cell C11**to determine the**Sold Number of Units**.

`=MIN(C10/C6,C9)`

**the MIN function**to get the minimum value.

- Then, find out the
**Revenue**in**Cell C12**with this formula.

`=C5*C11`

- Following this, calculate
**Expenses**in**Cell C13**with this formula.

`=C4*C9+C10`

- Lastly, apply this formula to calculate
**Profit**in**Cell C12**.

`=C12-C13`

- Along with it, apply the same process for
**Publisher 2**and get the following output.

- Next, we will determine the constraints in separate cells.
- To do this, count the total
**Budget for Advertisement**with this formula and determine as**Constraint 1**in**Cells C16**and**D16**.

`=C10+D10`

- Then, use the following cell references as
**Constraint 2**for**Publisher 1**. - Type the formula below in
**Cell C17**:

`=C11`

- Also, type the following in
**Cell D17**:

`=C7`

- Lastly, we need to insert cell references as
**Constraint 3**.

- To do so, write the formula below in
**Cell C18**:

`=D11`

- Also, type the following in
**Cell D18**:

`=D7`

- Alon with this, count the
**Total Profit**in**Cell C19**with this formula.

`=C14+D14`

- Up next, provide cell references in the
**Set Objective**and**By Changing Variable Cells**in the**Solver Parameters**dialogue box.

- Then, insert the first
**Constraint**and its relevant**Cell Reference**like this.

- Afterward, keep adding new constraints by pressing
**Add**in the**Add Constraint**window and click on**OK**when it is done. - Finally, you will see all the conditions in the
**Subject to the Constraints**box.

- Along with this, select
**Simplex LP**as the solving method.

- Thereafter, press
**Solve**. - That’s it, you will see that the
**Total Profit**is optimized and the**Budget for Advertisement**also changed accordingly.

**Read More: ****How to Solve Linear Optimization Model in Excel**

### 3. Nonlinear Problem Optimization with Constraints in Excel

In this last case, we will consider the popular** Magic Square **puzzle where we will do optimization. Here, we need to calculate the horizontal, vertical and diagonal values which sum up to **15 **which is the constraint. To do this, follow the steps below.

- In the beginning, insert the numbers from
**1**to**9**in the**Cell range B5:D7**like this.

- Then, count the total value of the first row with this formula in
**Cell E5**.

`=SUM(B5:D5)`

**the SUM function**to get the total value.

- Afterward, use the
**AutoFill**tool to calculate the**Sum of Rows**for**Rows 2**and**3**.

- Next, calculate the
**Sum of Column**for the**1st**column with this formula.

`=SUM(C5:C7)`

- Then, apply a similar process to the other
**2**columns.

- Lastly, calculate
**Diagonal Sum**with this formula for left angle diagonal values.

`=SUM(C5,D6,E7)`

- Similarly, count the same for right angle diagonal values.

`=SUM(E5,D6,C7)`

- Next, open the
**Solver Parameters**window and insert the**Variable Cell**reference.

- After this, provide the
**Constraints**as shown below.

- Thereafter, keep
**GRG Nonlinear**as the solving method.

- Lastly, press
**Solve**. - As a result, you will notice that the values in
**Numeric Inputs**are optimized and therefore their cumulative values are**15**on each side.

**Read More: ****How to Do Portfolio Optimization Using Excel Solver**

## Conclusion

That’s all for today. Get the sample file to learn the process of excel optimization with constraints through **3** case scenarios. Let us know your feedback in the comment box. Learn more about Excel with **ExcelDemy**.