The article will show you how to solve a **blending Linear Programming** problem with Excel **Solver**. Linear programming is an important application when we want to minimize cost or maximize profit in the business sector. It’s also important for our daily life as we should optimize our food habits and other expenditures. **Blending Linear Programming **is a special kind of **Linear Programming **where profit or cost optimization is calculated on one or multiple products that are made from the mixture of other raw materials. We are going to use Excel to apply this **Blending Linear Programming** to two real life examples to illustrate how this works.

**Table of Contents**hide

## Download Practice Workbook

## What Is Blending Linear Programming?

If you work in a chemical industry or food factory, you need to make products by mixing various materials. For example, if you want to produce a medicine, you need the necessary elements for it and you have to mix them in a defined proportion. In addition, you have to keep in mind how many of these elements you need to buy, how your products’ quality will be, and so on. In this case, you need to optimize the mixing amount of the products that you want to deliver to the customers. The solution to this problem is provided by the **Blending Linear Programming **application. This application helps us learn how to use raw materials in production.

## 2 Examples to Solve Blending Linear Programming Problem with Excel Solver

In this article, we are going to explain two types of examples for **Blending Linear Programming**. One example is to show how to solve **Blending LP **(**Linear Programming**) for **Fixed Recipe **and the other one is for **Flexible Recipe**.

**Fixed Recipe **refers to the scenario where you know the exact ratio or mixing amount of the materials. On the other hand, if you don’t have to use a defined amount of raw materials in the production of a commodity, then you should apply **Blending LP **for **Flexible Recipe**.

The first example is dedicated to show you how to solve the **Fixed Recipe **problem. Here, we have three types of liquid materials **A**,** B**, and **C**. We will make two new products using **A-B **and **A-C** with a combination of **60%-40% **and **80%-20%** respectively. There are other parameters like **Revenue/Liter**, **Cost/Liter**, and the number of raw materials available in the factory. We will **maximize **the profit out of this scenario.

For the **Flexible Recipe **problem, we will produce different types of steel like **regular**, **exclusive **and **super quality **steel from the mixture of different raw materials. We have the data about how much of these raw materials are available, the cost of these raw materials per ton, and the quality rating of them. We also set the required production amount, price per ton of produced different classes of steels and their minimum rating. There is another parameter which we call the **Linearized Rating**.

**1. Blending Linear Programming for Fixed Recipe Problem**

We have discussed this problem in the previous section. Let’s just go through the process below.

**Steps:**

- First, we will be setting some necessary formulas. We want to know about the
**Raw Usage**of the material and use the following formula in cell**C12**to calculate it.

`=SUMPRODUCT(C5:C9,$G$5:$G$9)`

Here we used **the SUMPRODUCT function **which will return the **Raw Usage **of material **A**.

- After that, drag the
**fill icon**to the right to**AutoFill**the cells up to**E12**.

- Later, use the following formula in
**I11**to calculate the**Revenue**.

`=SUMPRODUCT(G5:G9,H5:H9)*C16`

Here, we multiplied the formula by the value of cell **C16 **which is **3.7854 **because one gallon equals **3.7854 **Liter.

- Next, type the following formula in cell
**I12**and press**ENTER**.

`=SUMPRODUCT(C11:E11,C12:E12)*C16`

This formula will return the cost of production.

- Thereafter, we apply the following formula to calculate the
**profit**.

`=I11-I12`

- Now, set your minimum requirement of production.

- After that, go to
**Data**>>**Solver**. If you don’t know how to add**Solver Add-in**in the**Data Tab**, select**File**>>**Options**>>**Add-ins**>>**Excel Add-ins**>>**Go**>>**Solver Add-in**and click**OK**or follow this**Link**. - To open the
**Solver Add-in**, go to**Data**>>**Solver**.

- We want to maximize our profit so we set the objective reference to
**I13**where we stored the**Profit** - Also, our variable is the mixing percentage of the products. So we added the
**G5:G9**range to ‘**By Changing Variable Cells**’ section. - After that, click on
**Add**to add the**constraints**.

- The
**Raw Material Usage**cannot exceed the**Available Material**So our first constraint is that the range**C12:E12**will be less than or equal to**C14:E14**. - After that, click on
**Add**.

- Similarly, we added another constraint which implies that the production amount is greater than the minimum required production amount.
- Next, click
**OK**.

- After that, check ‘
**Make Unconstrained Variable Non-Negative**’. - Thereafter, select
**Simplex LP**as the**Solving Method**. - Later, click on
**Solve**.

- After that, a confirmation message box will appear. Just click
**OK**.

- Finally, you will get the values of how much
**Raw Materials**you should use to get the**Maximum Profit**. - In addition, you will also get the results of
**Revenue**,**Cost**of production, and**Profit**

Thus you can solve the **Blending Linear Programming **problem with Excel Solver for the **Fixed Recipe**.

**2. Excel Solver for Flexible Recipe Blending Linear Programming Problem**

In this section, I’ll show you how to solve the **Blending Linear Programming Problem **for the **Flexible Recipe**. To know about this problem, please go to this **link **of this article. Now, let’s go through the following description.

**Steps:**

- First, we are going to set up some formulas for our solution. Type the following formula in cell
**F5**and press the**ENTER**

`=SUM(C5:E5)`

The formula uses **the SUM function **to calculate the total amount of the type 1 **Steel **(**Regular**, **Exclusive **and **Super**) that will be produced from the first **Available **resources.

- Next, drag the
**Fill**icon below to fill the cells up to**F7**.

- Later, type the following formula for the calculation of total
**Regular Steel**production amount.

`=SUM(C5:C7)`

- Similarly, write down the formula below in cell
**C14**to calculate**Linearized Rating**and fill the adjacent cells up to**E14**.

`=SUMPRODUCT($J$5:$J$7,C5:C7)`

- After that, type the following formula
**C16**and fill the cells up to**E16**.

`=C12*C8`

The formula will give us the **Linearized Rating **of the produced **Steels**.

- Next, write down the formula below in cell
**I10**to determine the**Revenue**.

`=SUMPRODUCT(C11:E11,C8:E8)`

- To calculate the production cost, use the following formula.

`=SUMPRODUCT(I5:I7,F5:F7)`

- And the following formula will return the
**Profit**

`=I10-I11`

- After that, to enter the
**Subject**,**Changing Variable**and**Constraints**, please follow the**link**of**Method 1**that will lead you to the process. I’ll simply explain these inequalities in the following description. - We want to maximize the profit amount, so we reference the cell that contains profit (
**I12**). - Next, our changing variables are the
**Steel**products, so this range will be**C5:E7**where the amount of production will be stored. - After that, we added some constraints. The
**Linearized Raw Rating**will be greater than or equal to**Linearized Minimum Required Rating**, so the range**C14:E14**will be greater or equal to**C16:E16**. - Thereafter, the production amount will be greater than the required amount. So the range
**C8:E8**will be greater than**C10:E10**. - And the usage of raw materials will be lower than the available raw materials. So the range
**F5:F7**will be greater than**H5:H7**.

- After clicking on
**Solve**, you will get the values of how much**Raw Materials**you should use to get the**Maximum Profit**. - In addition, you will also get the results of
**Revenue**,**Cost**of production and**Profit**.

Thus you can solve the **Blending Linear Programming **problem with Excel Solver for the **Flexible Recipe**.

## Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

## Conclusion

Suffice to say, you will achieve the basic idea of how to apply **Blending Linear Programming **to solve real life optimization problems with **Excel Solver**.** **If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website **ExcelDemy**.