Excel Solver is used, to optimize equations based on the assumption in Excel. In this article, we will discuss how to do multi-objective optimization using the Excel Solver feature with proper illustrations.

**Table of Contents**Expand

**What Is Multi-Objective Optimization?**

The solution or optimal value of an equation comes from the optimization process. This optimization may have single or multiple objectives. This is quite popular in different sectors like engineering, mathematics, economics, etc.

When we get to perform the optimization process based on multiple objectives, it’s called **multi-objective optimization**.

For example, we consider the following situation. A super shop company has **5** stores. Now, the company wants to set up a warehouse in an optimum place that will be suitable for all the stores. With multiple conditions, the warehouse with a maximum and minimum distance from each store to minimize transportation costs. Another one is there is a dumping station in between the area of the store. We must avoid the dumping station. So, we will keep the maximum distance from this dumping station.

**How to Perform Multi-Objective Optimization with Excel Solver: Step-by-Step **

In this article, we will show how to perform multi-objective optimization with **Excel Solver**. We will consider the example mentioned in the previous section. We will find out the optimal place to set up the warehouse.

**Step 1: Set Up Variables and Conditions**

- In the first step, we set up the condition and variables in the dataset.

We set the maximum distance of **40 Km** and minimum distance of **5 Km**. We also set the location of the dumping station. Now, we will determine the value of the decision variables section.

**Read More: **Schedule Optimization in Excel

**Step 2: Calculate the Distance of the Stores from the Warehouse**

Here, we will calculate the distance from each store to the warehouse.

- Initially, consider the location of the warehouse is (
**X,Y**)=(**0,0**). - Look at the formula used in the
**Distance**column.

`=SQRT(($C$12-C5)^2+($D$12-D5)^2)`

Here, we used the distance formula of two coordinates.

- After that, sum all the distances on
**Cell E12**using the following formula.

`=SUM($E$5:$E$9)`

- We also calculate the distance of the dumping station to the warehouse on
**Cell J5**using the following formula.

`=SQRT((C12-H5)^2+(D12-I5)^2)`

This is the distance of the dumping station considering the location of the warehouse at (**0,0**).

**Read More:** How to Perform Route Optimization in Excel

**Step 3: Set Goals in the Dataset **

Here, we will set up the first two goals.

- Those are the ratio of the sum of the distance from each store and the dumping station distance. We will get them on
**Cell C14**and**C15**. - We used the following formulas.

**On Cell C14:**

`=E12/J5`

**On Cell C15:**

`=J5/E12`

We set up the minimum and maximum values.

**Step 4: Enable Excel Solver**

Now, we will enable the **Excel Solver** add-in.

- Go to
**File >> Options >> Add-ins**. - Choose
**Excel Add-ins**from the**Manage**section. - Then, press the
**Go**button.

- Choose
**Solver****Add-in**from the**Add-ins**window. - Then, press the
**OK**button.

- Go to the
**Data**tab now.

We can see the **Solver** add-in is present in the main tab.

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

**Step 5: Use Solver to Calculate Goal-1 and Goal-2**

In this section, we will apply the **Solver** to calculate the goals.

- Click on the
**Solver**option as shown before. - The
**Solver Parameters**window appears. **Set Objective**as**Cell C14**.- Select the
**Min**option as we want to get the minimum value. - Choose
**Range C12:D12**as the variable range. - Click on the
**Add**option.

- The
**Add Constraint**window appears. - We will set the conditions.
- We want to set distance of stores from
**40**to**5 Km**. We will set the cell reference here. - To add multiple conditions click on the
**Add**button. - After adding all the conditions click on the
**OK**button.

- Look at the window.
- Unmark the
**Make Unconstrained Variables Non-Negative**option. - Choose
**GRG Nonlinear**as the solving method. - Finally, click the
**Solve**button.

- Then, the
**Solver****Results**window appears. - Choose the
**Keep Solver Solution**option. - Then, press the
**OK**button.

- We can see the value of
**Goal 1**changes due to the**Solver**. - Copy the value of
**Cell C14**by pressing**Ctrl+C**. - Then, click on the down arrow of the
**Paste**field. - Select the
**Values(V)**from the**Paste Values**section.

- Then, clear the values of
**C12**and**D12**. Now, we will determine the value of**Goal 2**using the**Solver**.

- Then, choose the
**Cell C15**corresponding value of**Goal 2**as the**Objective**. - Select the
**Max**option. - Other conditions remain the same.
- Press the
**Solve**button.

- Look at the dataset.

We get the maximum value of **Goal 2**.

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

**Step 6: Determine the Optimum Distances**

Here, we will determine the optimum values of distances **D1** and **Dd**.

- First, add two new rows in the dataset for optimum values.

- Then, click on the
**Solver**shown before. - We will determine the minimum value for
**D1**in the first instance. - Set
**Cell E12**as the object and choose the**Min**option. - Constraints remain the same as set before.
- Click on the
**Solve**button and look at the dataset.

- We can see the value has been changed.

- Copy and paste only the values of
**Cell E12**to**Cell C17**shown before.

- Apply the similar
**Solver**operation for the maximum value of**Dd**. - Choose
**Cell J5**as the objective and choose the**Max**option. - Finally, click on the
**Solve**button.

- Similarly, copy the maximum value of
**Dd**at**Cell C18**.

**Step 7: Determine the Weighted Values**

In this section, we will find out the weighted value for **Goal 3**. Weight values get based on the equation:

`W(D1)*ABS((Optimum D1-Actual D1))/Actual D1+ W(Dd)*ABS((Optimum Dd-Actual Dd))/Actual Dd`

- First, we the weight section in the dataset.
- We set the values of
**W1**as**1,6,10**and**W2**as**1,1,1**.**W1**is for**D1**, and**W2**is for**Dd**.

- Now, set the
**W1=1**and**W2=1**and enter the following equation on**Cell C16**.

`=D19*(ABS(E12-C19)/C19)+D20*(ABS(J5-C20)/C20)`

- Now, we will use the
**Solver**. - Set
**Cell C16**as the object and**Min**option. - The rest of the variables and constraints remain the same.
- Finally, press the
**Solve**button.

- Due to the solver, the values of
**Xf**,**Yf**,**D1**, and**Dd**have been changed.

- Now, copy only the values of
**Xf**,**Yf**,**D1**, and**Dd**in the Weighted value table.

- Now, change the value of the
**W1**to**6**and**10**and**W2**is always**1**.

- Again, apply the
**Solver**operation and copy the value in the table.

There are three coordinates of (**Xf, Yf**). We can set the warehouse at any of the given three points.

**Download Practice Workbook**

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

**Conclusion**

In this article, the multi-objective optimization process. Then, we solve that problem using Excel Solver. We also added a graph for better understanding. I hope this will satisfy your needs.

## Related Articles

- How to Make Price Optimization Models in Excel
- Excel Optimization with Constraints
- How to Optimize Multiple Variables in Excel
- How to Calculate Optimal Product Mix in Excel
- Mean Variance Optimization in Excel

**<< Go Back to Optimization in Excel | Solver in Excel | Learn Excel**