How to Perform Multi-Objective Optimization with Excel Solver

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.

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

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.

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.

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.

Multi-Objective Optimization.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!