assignment problem solver excel

#1 Excel tutorial on the net

• Assignment Problem

Formulate the Model | Trial and Error | Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this assignment problem , answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Note: can't find the Solver button? Click here to load the Solver add-in .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

8. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

9. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.

Learn more, it's easy

• Transportation Problem
• Shortest Path Problem
• Maximum Flow Problem
• Capital Investment
• Sensitivity Analysis
• System of Linear Equations

Download Excel File

• assignment-problem.xlsx

Next Chapter

• Analysis ToolPak

Follow Excel Easy

Become an Excel Pro

• 300 Examples

Assignment Problem • © 2010-2024 Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting

• Ablebits blog
• Financial functions

How to use Solver in Excel with examples

The tutorial explains how to add and where to find Solver in different Excel versions, from 2016 to 2003. Step-by-step examples show how to use Excel Solver to find optimal solutions for linear programming and other kinds of problems.

Everyone knows that Microsoft Excel contains a lot of useful functions and powerful tools that can save you hours of calculations. But did you know that it also has a tool that can help you find optimal solutions for decision problems?

In this tutorial, we are going to cover all essential aspects of the Excel Solver add-in and provide a step-by-step guide on how to use it most effectively.

What is Excel Solver?

Excel Solver belongs to a special set of commands often referred to as What-if Analysis Tools. It is primarily purposed for simulation and optimization of various business and engineering models.

The Excel Solver add-in is especially useful for solving linear programming problems, aka linear optimization problems, and therefore is sometimes called a linear programming solver . Apart from that, it can handle smooth nonlinear and non-smooth problems. Please see Excel Solver algorithms for more details.

How to add Solver to Excel

The Solver add-in is included with all versions of Microsoft Excel beginning with 2003, but it is not enabled by default.

To add Solver to your Excel, perform the following steps:

• In Excel 2010 - Excel 365, click File > Options . In Excel 2007, click the Microsoft Office button, and then click Excel Options .

To get Solver on Excel 2003 , go to the Tools menu, and click Add-Ins . In the Add-Ins available list, check the Solver Add-in box, and click OK .

Where is Solver in Excel?

Where is Solver in Excel 2003?

Now that you know where to find Solver in Excel, open a new worksheet and let's get started!

How to use Solver in Excel

Before running the Excel Solver add-in, formulate the model you want to solve in a worksheet. In this example, let's find a solution for the following simple optimization problem.

Problem . Supposing, you are the owner of a beauty salon and you are planning on providing a new service to your clients. For this, you need to buy a new equipment that costs $40,000, which should be paid by instalments within 12 months.

Goal : Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.

And now, let's see how Excel Solver can find a solution for this problem.

1. Run Excel Solver

2. define the problem.

The Solver Parameters window will open where you have to set up the 3 primary components:

• Objective cell

Variable cells

Constraints.

Exactly what does Excel Solver do with the above parameters? It finds the optimal value (maximum, minimum or specified) for the formula in the Objective cell by changing the values in the Variable cells, and subject to limitations in the Constraints cells.

The Objective cell ( Target cell in earlier Excel versions) is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.

Variable cells ( Changing cells or Adjustable cells in earlier versions) are cells that contain variable data that can be changed to achieve the objective. Excel Solver allows specifying up to 200 variable cells.

In this example, we have a couple of cells whose values can be changed:

• Projected clients per month (B4) that should be less than or equal to 50; and
• Cost per service (B5) that we want Excel Solver to calculate.

The Excel Solver Constrains are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.

To add a constraint(s), do the following:

• Click the Add button right to the " Subject to the Constraints " box.

• In the Constraint window, enter a constraint.
• Click the Add button to add the constraint to the list.

• Continue entering other constraints.
• After you have entered the final constraint, click OK to return to the main Solver Parameters window.

Excel Solver allows specifying the following relationships between the referenced cell and the constraint.

• Less than or equal to , equal to , and greater than or equal to . You set these relationships by selecting a cell in the Cell Reference box, choosing one of the following signs: <= , =, or >= , and then typing a number, cell reference / cell name, or formula in the Constraint box (please see the above screenshot).
• Integer . If the referenced cell must be an integer, select int , and the word integer will appear in the Constraint box.
• Different values . If each cell in the referenced range must contain a different value, select dif , and the word AllDifferent will appear in the Constraint box.
• Binary . If you want to limit a referenced cell either to 0 or 1, select bin , and the word binary will appear in the Constraint box.

To edit or delete an existing constraint do the following:

• In the Solver Parameters dialog box, click the constraint.
• To modify the selected constraint, click Change and make the changes you want.
• To delete the constraint, click the Delete button.

In this example, the constraints are:

• B3=40000 - cost of the new equipment is $40,000.
• B4<=50 - the number of projected patients per month in under 50.

3. Solve the problem

After you've configured all the parameters, click the Solve button at the bottom of the Solver Parameters window (see the screenshot above) and let the Excel Solver add-in find the optimal solution for your problem.

Depending on the model complexity, computer memory and processor speed, it may take a few seconds, a few minutes, or even a few hours.

The Solver Result window will close and the solution will appear on the worksheet right away.

• If the Excel Solver has been processing a certain problem for too long, you can interrupt the process by pressing the Esc key. Excel will recalculate the worksheet with the last values found for the Variable cells.
• To get more details about the solved problem, click a report type in the Reports box, and then click OK . The report will be created on a new worksheet:

Excel Solver examples

Below you will find two more examples of using the Excel Solver addin. First, we will find a solution for a well-known puzzle, and then solve a real-life linear programming problem.

Excel Solver example 1 (magic square)

I believe everyone is familiar with "magic square" puzzles where you have to put a set of numbers in a square so that all rows, columns and diagonals add up to a certain number.

For instance, do you know a solution for the 3x3 square containing numbers from 1 to 9 where each row, column and diagonal adds up to 15?

It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster. Our part of the job is to properly define the problem.

With all the formulas in place, run Solver and set up the following parameters:

• Set Objective . In this example, we don't need to set any objective, so leave this box empty.
• Variable Cells . We want to populate numbers in cells B2 to D4, so select the range B2:D4.
• $B$2:$D$4 = AllDifferent - all of the Variable cells should contain different values.
• $B$2:$D$4 = integer - all of the Variable cells should be integers.
• $B$5:$D$5 = 15 - the sum of values in each column should equal 15.
• $E$2:$E$4 = 15 - the sum of values in each row should equal 15.
• $B$7:$B$8 = 15 - the sum of both diagonals should equal 15.

Excel Solver example 2 (linear programming problem)

This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.

Problem : You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand.

Goal : Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer.

Source data

Formulating the model

To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:

• What decisions are to be made? We want to calculate the optimal quantity of goods to deliver to each customer from each warehouse. These are Variable cells (B7:E8).
• What are the constraints? The supplies available at each warehouse (I7:I8) cannot be exceeded, and the quantity ordered by each customer (B10:E10) should be delivered. These are Constrained cells .
• What is the goal? The minimal total cost of shipping. And this is our Objective cell (C12).

To make our transportation optimization model easier to understand, create the following named ranges:

The last thing left for you to do is configure the Excel Solver parameters:

• Objective: Shipping_cost set to Min
• Variable cells: Products_shipped
• Constraints: Total_received = Ordered and Total_shipped <= Available

How to save and load Excel Solver scenarios

When solving a certain model, you may want to save your Variable cell values as a scenario that you can view or re-use later.

For example, when calculating the minimal service cost in the very first example discussed in this tutorial, you may want to try different numbers of projected clients per month and see how that affects the service cost. At that, you may want to save the most probable scenario you've already calculated and restore it at any moment.

Saving an Excel Solver scenario boils down to selecting a range of cells to save the data in. Loading a Solver model is just a matter of providing Excel with the range of cells where your model is saved. The detailed steps follow below.

Saving the model

To save the Excel Solver scenario, perform the following steps:

• Open the worksheet with the calculated model and run the Excel Solver.

• Excel will save your current model, which may look something similar to this:

Loading the saved model

When you decide to restore the saved scenario, do the following:

• In the Solver Parameters window, click the Load/Save button.

• This will open the main Excel Solver window with the parameters of the previously saved model. All you need to do is to click the Solve button to re-calculate it.

Excel Solver algorithms

When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:

• GRG Nonlinear. Generalized Reduced Gradient Nonlinear algorithm is used for problems that are smooth nonlinear, i.e. in which at least one of the constraints is a smooth nonlinear function of the decision variables. More details can be found here .
• LP Simplex . The Simplex LP Solving method is based the Simplex algorithm created by an American mathematical scientist George Dantzig. It is used for solving so called Linear Programming problems - mathematical models whose requirements are characterized by linear relationships, i.e. consist of a single objective represented by a linear equation that must be maximized or minimized. For more information, please check out this page .
• Evolutionary . It is used for non-smooth problems, which are the most difficult type of optimization problems to solve because some of the functions are non-smooth or even discontinuous, and therefore it's difficult to determine the direction in which a function is increasing or decreasing. For more information, please see this page .

This is how you can use Solver in Excel to find the best solutions for your decision problems. At the end of this post, you can download the sample workbook with all the examples discussed in this tutorial and reverse-engineer them for better understanding. I thank you for reading and hope to see you on our blog next week.

Practice workbook for download

You may also be interested in.

• Using Excel Goal Seek for What-If analysis
• Excel Copilot with examples
• Linear regression analysis in Excel
• Microsoft Excel formulas with examples
• How to use VLOOKUP & SUM or SUMIF functions in Excel

Table of contents

Excel Tutorial: How To Solve Assignment Problem In Excel

Introduction.

Have you ever faced the challenge of assigning a set of tasks to a group of resources in Excel? This is what we call the assignment problem in Excel. It is a common issue faced by many professionals, and finding an efficient solution can greatly improve productivity and resource utilization. In this tutorial, we will guide you through the process of solving the assignment problem in Excel, and explain why it is important to master this skill.

Key Takeaways

• Solving the assignment problem in Excel can greatly improve productivity and resource utilization.
• Understanding the assignment problem and its real-life applications is important for professionals.
• Organizing and managing data accurately is crucial for solving the assignment problem in Excel.
• Using the Solver tool effectively can help find optimal solutions for the assignment problem.
• Efficiency tips and avoiding common mistakes can streamline the problem-solving process in Excel.

Understanding the Assignment Problem

The assignment problem is a fundamental optimization problem that involves finding the most efficient assignment of tasks to resources. In other words, it is the process of finding the best possible way to allocate a set of resources to a set of tasks in such a way that the overall cost or time is minimized.

The assignment problem can be defined as a special case of the transportation problem, where the objective is to minimize the cost of assigning a set of tasks to a set of resources, given certain constraints and limitations.

There are different types of assignment problems, including the balanced assignment problem, unbalanced assignment problem, and the generalized assignment problem. The balanced assignment problem occurs when the number of resources is equal to the number of tasks, while the unbalanced assignment problem occurs when the number of resources is not equal to the number of tasks. The generalized assignment problem allows for assigning a task to multiple resources, but with different costs associated with each assignment.

The assignment problem has numerous real-life applications, such as in workforce scheduling, project management, and supply chain optimization. In Excel, the assignment problem can be solved using various optimization techniques and algorithms, such as the Hungarian method, the auction algorithm, and the shortest path algorithm. These techniques can be implemented using Excel's built-in solver tool, which allows users to find the optimal solution to assignment problems by minimizing a given objective function, subject to certain constraints.

Setting up the Data in Excel

When it comes to solving the assignment problem in Excel, setting up the data properly is crucial for accurate and efficient analysis. In this chapter, we will discuss how to organize the data, use Excel tables for better data management, and ensure data accuracy and completeness.

• Identify the assignment problem variables and constraints
• Create a separate section in Excel for each variable and constraint
• Ensure the data is organized in a clear and logical manner for easy analysis
• Create an Excel table for each section of the assignment problem
• Use table features such as filtering and sorting to easily manipulate and analyze the data
• Utilize the structured format of tables for improved data organization and readability
• Double-check all data entries for accuracy
• Verify that all variables and constraints are accounted for in the data
• Use validation and error-checking features in Excel to minimize data errors

Using Solver Tool in Excel

Excel’s Solver tool is a powerful feature that allows users to find the optimal solution to complex problems, such as the assignment problem. By utilizing the Solver tool, you can efficiently allocate resources and optimize various aspects of your business or project.

Accessing the Solver tool in Excel

To access the Solver tool in Excel, you first need to ensure that it is installed as an add-in. You can do this by navigating to the “File” tab, selecting “Options,” and then clicking on “Add-Ins.” From there, you can enable the Solver add-in, which will then appear in the “Data” tab under the “Analysis” group.

Defining the objective and constraints

Once the Solver tool is accessible, you can begin defining the objective and constraints of your assignment problem. The objective is the goal you want to achieve, such as maximizing profits or minimizing costs. Constraints are the limitations or restrictions that must be considered, such as resource availability or capacity.

Running the Solver to find the optimal solution

After defining the objective and constraints, you can then run the Solver to find the optimal solution to your assignment problem. The Solver tool will utilize algorithms to analyze various combinations and iterations to determine the best possible outcome based on the defined parameters.

Interpreting the Results

After using Excel to solve the assignment problem, it is important to understand and interpret the results to ensure accuracy and make any necessary adjustments.

• Objective Function: The Solver provides the optimal value of the objective function, which represents the minimum cost or maximum profit.
• Variable Values: It also provides the optimal values for the decision variables, indicating the allocation of resources to tasks.
• Check Constraints: Ensure that all constraints are satisfied by the solution, such as resource limits and task requirements.
• Sensitivity Analysis: Conduct sensitivity analysis to understand how changes in inputs or constraints affect the solution.
• Scenario Analysis: Explore different scenarios by adjusting input values or constraints to evaluate alternative solutions.
• Iterative Process: If the initial solution is not optimal, iterate by making adjustments and re-solving the problem using Solver.

Tips for Efficiency

Efficiency is key when solving assignment problems in Excel. By utilizing shortcuts, functions, and avoiding common mistakes, you can streamline the process and save valuable time.

• Keyboard shortcuts: Familiarize yourself with common keyboard shortcuts in Excel to quickly perform functions such as copying, pasting, and formatting.
• Use of built-in functions: Take advantage of Excel's built-in functions such as VLOOKUP, INDEX, and MATCH to streamline the assignment problem-solving process.
• Organize data: Before starting to solve the assignment problem, ensure that your data is properly organized and formatted to avoid confusion and errors.
• Utilize templates: Consider creating templates or using pre-existing ones to simplify the process and avoid starting from scratch each time.
• Double-check formulas: Always double-check your formulas to avoid errors that can lead to incorrect solutions.
• Use of absolute references: When working with formulas, use absolute references to ensure that cell references do not change unintentionally.

Recap: Solving assignment problems in Excel is crucial for efficient project management and decision-making. It allows for optimal allocation of resources and time, leading to streamlined workflows and increased productivity.

Encouragement: Practice makes perfect, and the same goes for mastering Excel. I encourage you to continue practicing and applying the tutorial steps to become proficient in solving assignment problems in Excel.

Reiteration: Mastering Excel for problem-solving offers numerous benefits, including improved data analysis, better decision-making, and enhanced project management skills. By honing your Excel skills, you can excel in your professional and academic endeavors.

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

The Benefits of Excel Dashboards for Data Analysts

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

Unlocking the Potential of Excel's Data Dashboard

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Unleashing the Power of Excel Dashboards

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Leverage Your Data with Excel Dashboards

Crafting the Perfect Dashboard for Excel

An Introduction to Excel Dashboards

How to Create an Effective Excel Dashboard

• Choosing a selection results in a full page refresh.

Define and solve a problem by using Solver

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.

Example of a Solver evaluation

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the total profit (objective cell F7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell F7, =SUM (Q1 Profit:Q2 Profit).

1. Variable cells

2. Constrained cell

3. Objective cell

After Solver runs, the new values are as follows.

Define and solve a problem

Note:  If the Solver command or the Analysis group is not available, you need to activate the Solver add-in. See: How to activate the Solver add-in.

In the Set Objective box, enter a cell reference or name for the objective cell. The objective cell must contain a formula.

Do one of the following:

If you want the value of the objective cell to be as large as possible, click Max .

If you want the value of the objective cell to be as small as possible, click Min .

If you want the objective cell to be a certain value, click Value of , and then type the value in the box.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the non-adjacent references with commas. The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, enter any constraints that you want to apply by doing the following:

In the Solver Parameters dialog box, click Add .

In the Cell Reference box, enter the cell reference or name of the cell range for which you want to constrain the value.

Click the relationship ( <= , = , >= , int , bin , or dif ) that you want between the referenced cell and the constraint.If you click int , integer appears in the Constraint box. If you click bin , binary appears in the Constraint box. If you click dif , alldifferent appears in the Constraint box.

If you choose <=, =, or >= for the relationship in the Constraint box, type a number, a cell reference or name, or a formula.

To accept the constraint and add another, click Add .

To accept the constraint and return to the Solver Parameter s dialog box, click OK . Note     You can apply the int , bin , and dif relationships only in constraints on decision variable cells.

You can change or delete an existing constraint by doing the following:

In the Solver Parameters dialog box, click the constraint that you want to change or delete.

Click Change and then make your changes, or click Delete .

Click Solve and do one of the following:

To keep the solution values on the worksheet, in the Solver Results dialog box, click Keep Solver Solution .

To restore the original values before you clicked Solve , click Restore Original Values .

You can interrupt the solution process by pressing Esc. Excel recalculates the worksheet with the last values that are found for the decision variable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK . The report is created on a new worksheet in your workbook. If Solver doesn't find a solution, only certain reports or no reports are available.

To save your decision variable cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

Step through Solver trial solutions

After you define a problem, click Options in the Solver Parameters dialog box.

In the Options dialog box, select the Show Iteration Results check box to see the values of each trial solution, and then click OK .

In the Solver Parameters dialog box, click Solve .

In the Show Trial Solution dialog box, do one of the following:

To stop the solution process and display the Solver Results dialog box, click Stop .

To continue the solution process and display the next trial solution, click Continue .

Change how Solver finds solutions

In the Solver Parameters dialog box, click Options .

Choose or enter values for any of the options on the All Methods , GRG Nonlinear , and Evolutionary tabs in the dialog box.

Save or load a problem model

In the Solver Parameters dialog box, click Load/Save .

Enter a cell range for the model area, and click either Save or Load .

When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. When you load a model, enter the reference for the entire range of cells that contains the problem model.

Tip:  You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook. Each worksheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a worksheet by clicking Load/Save to save problems individually.

Solving methods used by Solver

You can choose any of the following three algorithms or solving methods in the Solver Parameters dialog box:

Generalized Reduced Gradient (GRG) Nonlinear     Use for problems that are smooth nonlinear.

LP Simplex     Use for problems that are linear.

Evolutionary     Use for problems that are non-smooth.

Important:  You should enable the Solver add-in first. For more information, see Load the Solver add-in .

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell D5), until the total profit (objective cell D7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell D7, =SUM(Q1 Profit:Q2 Profit).

Click Data > Solver .

In Set Objective , enter a cell reference or name for the objective cell.

Note:  The objective cell must contain a formula.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the nonadjacent references with commas.

The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, add any constraints that you want to apply.

To add a constraint, follow these steps:

On the <= relationship pop-up menu, select the relationship that you want between the referenced cell and the constraint.If you choose <= , = , or >= , in the Constraint box, type a number, a cell reference or name, or a formula.

Note:  You can only apply the int, bin, and dif relationships in constraints on decision variable cells.

Click Solve , and then do one of the following:

To interrupt the solution process, press ESC . Excel recalculates the sheet with the last values that are found for the adjustable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK . The report is created on a new sheet in your workbook. If Solver doesn't find a solution, the option to create a report is unavailable.

To save your adjusting cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

After you define a problem, in the Solver Parameters dialog box, click Options .

Select the Show Iteration Results check box to see the values of each trial solution, and then click OK .

Click Options , and then in the Options or Solver Options dialog box, choose one or more of the following options:

In the Solver Parameters dialog box, click Solve or Close .

Click Load/Save , enter a cell range for the model area, and then click either Save or Load .

Tip:  You can save the last selections in the Solver Parameters dialog box with a sheet by saving the workbook. Each sheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a sheet by clicking Load/Save to save problems individually.

On the Select a Solving Method pop-up menu, select one of the following:

Note:  Portions of the Solver program code are copyright 1990-2010 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Because add-in programs aren’t supported in Excel for the web, you won’t be able to use the Solver add-in to run what-if analysis on your data to help you find optimal solutions.

If you have the Excel desktop application, you can use the Open in Excel button to open your workbook to use the Solver add-in .

More help on using Solver

For more detailed help on Solver contact:

Frontline Systems, Inc. P.O. Box 4288 Incline Village, NV 89450-4288 (775) 831-0300 Web site: http://www.solver.com E-mail: [email protected] Solver Help at www.solver.com .

Portions of the Solver program code are copyright 1990-2009 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Using Solver for capital budgeting

Using Solver to determine the optimal product mix

Introduction to what-if analysis

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Keyboard shortcuts in Excel

Excel functions (alphabetical)

Excel functions (by category)

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

• WordPress.org
• Documentation
• Learn WordPress

Browse through various articles and courses for Free at DeveloperPublish.com

• What is My IP Address?
• Password Generator

Balanced Assignment Problem Using Excel Solver

In this post, you’ll learn about Assignment problem and know how to solve a assignment problem using Excel Solver Add-In .

Assignment problem

An Assignment problem is a type of linear programming problem, which is about assigning the correct number of resources to various destinations or problems. This gives you the optimal solution which is either maximizing the profit or minimizing the cost of production .

How to Solve Balanced Assignment Problem Using Excel Solver Add-In?

Let’s look at the range of values in the screenshot attached below. We need to minimize the cost of production in this problem.

The Assigned values are the sum of the respective Customer value row, similarly the sum of Task columns for the Assigned. So use the =SUM() formula.

For finding the Optimal solution use the function =SUMPRODUCT() .

• After you’re done entering the values, go to the Data tab and under Analyze group select the Solver tool .
• Set the objective in the correct cell. Select Min in the To field.
• Changing variables are the tasks to the respective customer whose values aren’t determined still.
• The Constraints are Assigned = Supply , Assigned = Demand, and the range of the changing variables are binary .
• Finally select the solving method as Simplex LP and click on Solve .

Click on OK to get the solution.

A optimal solution is obtained.

Leave A Reply Cancel reply

Your email address will not be published. Required fields are marked *

You May Also Like

SKEW.P Function in Excel

• March 16, 2022

SKEW Function in Excel

RANK.EQ Function in Excel

• November 23, 2021

Login with your site account

Remember Me

• Advanced Tutorial
• Tips and Tricks

• MS Excel Tutorial

Solving Assignment Problems in Excel

Assignment problem.

Formulate the Model | Trial and Error | Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this assignment problem , answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Note: can’t find the Solver button? Click here to load the Solver add-in .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

8. Check ‘Make Unconstrained Variables Non-Negative’ and select ‘Simplex LP’.

9. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.

3/8 Completed! Learn much more about the solver >

Next Chapter: Analysis ToolPak

RELATED ARTICLES MORE FROM AUTHOR

Excel SUMIF Function: Conditional Data Summation

Excel sumproduct: versatile multiplication, excel today function and exemplary formulas, editor picks, excel borders and shades: enhancing cell appearance, ms word text formatting: style your content, features of ms excel: unleashing productivity, vba nested if statement: multilevel conditions, mac excel shortcuts: boosting productivity.

EVEN MORE NEWS

POPULAR CATEGORY

• Office Tools 674
• MS Excel Tutorial 429
• Functions 330
• Tips and Tricks 276
• Functions Examples 188
• Basic Tutorial
• VBA Examples
• Functions Examples
• Compatibility Excel Formulas & Functions
• Data Analysis

Excel Tutorial: Using Microsoft Query

• Basic Tutorial
• VBA Examples
• Functions Examples
• Compatibility Excel Formulas & Functions
• Tips and Tricks
• Data Analysis
• Other Tutorials

Assignment Problem in Excel

Assignment problem.

Formulate the Model | Trial and Error | Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this assignment problem , answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Note: can’t find the Solver button? Click here to load the Solver add-in .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

8. Check ‘Make Unconstrained Variables Non-Negative’ and select ‘Simplex LP’.

9. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.

3/8 Completed! Learn much more about the solver >

Next Chapter: Analysis ToolPak

RELATED ARTICLES MORE FROM AUTHOR

Create Charts in Excel

The only match function tutorial you need, median function in excel, mirr function in excel, how to use nper function in excel, ways that offset function is absolutely useful in business, editor picks, matrix multiplication in excel, chart with hours, gantt chart excel, hyperlink in excel, even more news.

Multi Level Pie Chart in Excel

How to count non blank cells in excel, multiple overlay charts in excel, popular category.

• Office Tools 674
• MS Excel Tutorial 429
• Functions 331
• Tips and Tricks 280
• Functions Examples 189

Add a Trendline in Excel

• PRO Courses Guides New Tech Help Pro Expert Videos About wikiHow Pro Upgrade Sign In
• EDIT Edit this Article
• EXPLORE Tech Help Pro About Us Random Article Quizzes Request a New Article Community Dashboard This Or That Game Popular Categories Arts and Entertainment Artwork Books Movies Computers and Electronics Computers Phone Skills Technology Hacks Health Men's Health Mental Health Women's Health Relationships Dating Love Relationship Issues Hobbies and Crafts Crafts Drawing Games Education & Communication Communication Skills Personal Development Studying Personal Care and Style Fashion Hair Care Personal Hygiene Youth Personal Care School Stuff Dating All Categories Arts and Entertainment Finance and Business Home and Garden Relationship Quizzes Cars & Other Vehicles Food and Entertaining Personal Care and Style Sports and Fitness Computers and Electronics Health Pets and Animals Travel Education & Communication Hobbies and Crafts Philosophy and Religion Work World Family Life Holidays and Traditions Relationships Youth
• Browse Articles
• Learn Something New
• Quizzes Hot
• This Or That Game
• Train Your Brain
• Explore More
• Support wikiHow
• About wikiHow
• Log in / Sign up
• Computers and Electronics
• Spreadsheets
• Microsoft Excel

How to Use Solver in Microsoft Excel

Last Updated: July 28, 2022 Tested

Enabling Solver

Analyzing and solving.

This article was co-authored by wikiHow staff writer, Jack Lloyd . Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. The wikiHow Tech Team also followed the article's instructions and verified that they work. This article has been viewed 618,636 times. Learn more...

This wikiHow teaches you how to use Microsoft Excel's Solver tool, which allows you to alter different variables in a spreadsheet in order to achieve a desired solution. You can use Solver in both Windows and Mac versions of Excel, though you'll have to enable Solver before you can begin using it.

• Solver comes pre-installed with both Windows and Mac versions of Excel, but you'll have to enable it manually.

• If you have an existing Excel file you'd like to use Solver with, you can open it instead of creating a new file.

• On a Mac, click Tools instead, then skip the next step.

• On a Mac, click Excel Add-ins in the Tools menu.

• On a Mac, this window will open after clicking Excel Add-ins in the Tools menu.

• For example, you might create a spreadsheet documenting your various expenses over the course of a month with the output cell resulting in your money left over.
• You can't use solver on a spreadsheet which doesn't have solvable data (i.e., your data has to have equations).

• For example, if you're creating a budget where the end goal is your monthly income, you would click the final "Income" cell.

• For example, if your goal is to have $200 at the end of the month, you would type 200 into the text box.
• You can also check either the "Max" or "Min" box in order to prompt Solver to determine the absolute maximum or minimum value.
• Once you've set a goal, Solver will attempt to meet that goal by adjusting other variables in your spreadsheet.

• Click the cell (or select the cells) for which the constraint applies.
• Select a type of constraint from the middle drop-down menu.
• Enter the constraint's number (e.g., a maximum or minimum).

• If you do like your Solver's results, you can apply them to your spreadsheet by checking the "Keep Solver Solution" box and then clicking OK .

Expert Q&A

• Solver is best used for problems such as scheduling employees, determining the lowest price for which you can sell items while meeting a financial goal, and budgeting. Thanks Helpful 1 Not Helpful 0

• Solver cannot be used in spreadsheets in which there is no "output" or actual solution. For example, you can't apply solver to a spreadsheet which has no equations. Thanks Helpful 1 Not Helpful 1

You Might Also Like

• ↑ https://support.office.com/en-us/article/Load-the-Solver-Add-in-in-Excel-612926fc-d53b-46b4-872c-e24772f078ca#OfficeVersion=Windows
• ↑ https://www.youtube.com/watch?v=dRm5MEoA3OI

About This Article

1. Enable Solver in the "Add-ins" section of your Excel preferences if necessary. 2. Open a spreadsheet with data you want to analyze. 3. Click Data , then click Solver . 4. Select a cell to use from the "Set Objective" field. 5. Check the "Value Of" box, then enter a desired value. 6. Click Solve . Did this summary help you? Yes No

• Send fan mail to authors

Is this article up to date?

Featured Articles

Trending Articles

Watch Articles

• Terms of Use
• Privacy Policy
• Do Not Sell or Share My Info
• Not Selling Info

wikiHow Tech Help Pro:

Level up your tech skills and stay ahead of the curve

#1 Excel tutorial on who net

• Assignment Problem

Formulate an View | Trial press Error | Solve this Model

Use the solver are Excel toward find the associations of persons to mission that minimizes the grand cost.

Frame the Exemplar

The model are are going to solve looks the follows in Excel.

1. To formulate this assignment problem , answer the following three frequent.

ampere. What represent the decisions go be made? Since this problem, we need Excel into find away which character into assign to which item (Yes=1, No=0). For example, if person assign Person 1 at Task 1, cell C10 corresponds 1. If not, cell C10 matches 0.

b. What are the constrains on these deciding? Each person can only go one work (Supply=1). Each task only needs one person (Demand=1).

hundred. What is the gesamtgewicht measure of performance for that decisions? The overall measure by performance is the total cost of the task, so the objective is to minimize the crowd.

2. To make the model easier to understand, create the following named ranges .

3. Insertable of following functions.

Explication: The SUM functions calculate the numeral of tasks assigned to a person and one number of personnel assigned to a task. Total Cost equals the sumproduct of Selling and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, whenever wealth assign Person 1 to Task 1, Soul 2 up task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assignments equals Call. This solution has a full cost of 147.

It is not necessary to use trial and error. We shall specify next how who Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find this optimised solution, execute this following steps.

1. On the Datas tab, into the Analyze group, click Solver.

Note: can't find of Solver mouse? Click here toward load the Solver add-in .

Enter this solver parameters (read on). The result should be uniformly with the picture below.

You will the selecting of typing the range names or clicking on the measuring in the spreadsheet.

2. Enter TotalCost required the Objective.

3. Click Min.

4. Enter Assignment with of Changing Variable Cells.

5. Please Add to please the following constraint.

Note: bin variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add into enter the following constraint.

8. Check 'Make Unconstrained Actual Non-Negative' and select 'Simplex LP'.

9. Finally, click Unsolve.

The optimal solution:

Conclusion: he is optimal to assign Person 1 to undertaking 2, Name 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost from 129. All constrictions are pleased. Class, For Module 8 Problem exercise #3, please | Chegg.com

Learn extra, it's easy

• Transportation Report
• Lowest Path Problem
• Maximum Fluid Problem
• Capital Investment
• Sensitivity Review
• System of Linear Equations

Download Excel File

• assignment-problem.xlsx

Next Chapter

• Analyzed ToolPak

Follow Outshine Easy

Become an Excel Pro

• 300 Examples

Assignment Problem • © 2010-2023 Excel is Awesome, we'll show they: Introduction • Essential • Functions • Data Analysis • VBA • 300 Examples

404 Not found

Optimization in Excel (A Complete Guide)

In this article, we serve as your guide to optimization in Excel. We will discuss the Solver and its activation in Excel. We will explore how to solve linear optimization models in Excel. Additionally, we will dive into examples that show the application of Excel’s Solver tool for optimization tasks.

With Excel’s Solver, users can optimize a wide range of parameters, such as production quantities, investment portfolios, resource allocations, scheduling, and more.

Here, we’ll show 6 different aspects of optimization in Excel through examples. So, come along as we explore the world of optimization in Excel one by one.

Download Practice Workbook

You may download the following attached workbook and follow it while going through the article. It’s also helpful for your practice.

What Is Solver in Excel?

Solver is like a special helper that you can add to your computer program like Excel. Actually, it’s an add-in. It’s really good at solving tricky problems by finding the best answer using math. This is made to solve problems where you want to find the maximum or minimum value while following some restrictions.

It is particularly good at solving linear programming problems, which are all about finding the best solution to maximize or minimize something. That’s why some people also refer to it as a linear programming solver.

But that’s not all it can do! It can also handle other types of problems that involve curves and bumpy situations. So, whether your problem is straightforward or a bit more complicated, Excel Solver is there to assist you.

How to Enable Solver in Excel?

• First, open the Excel Options window by clicking File >> Options .
• Select Add-ins on the left side panel.
• Then, choose Excel Add-ins in the Manage option and click on Go… .

• In the Add-ins dialog box, check the box of Solver Add-in and click OK .

6 Examples to Use Solver for Optimization in Excel

1. use solver for optimization without constraints.

Here, we’ll minimize the value of f(x) where f(x) = x 3 +3x 2 -5 . In this example, there are no constraints that we are using.

• In cell C7, we entered the following formula to replicate the equation of f(x) .

Here, C4 denotes the value of x .

• Then, navigate to the Data tab >> Solver option on the Analyze group.

It’ll open the Solver Parameters dialog box.

• In the Set Objective box, select cell C7 and choose Min in the below section as we want to minimize its value.
• Select cell C4 in the By Changing Variable Cells box and click Solve .

By the end, the value of x in cell C4 is converted to 0 and the minimum value of f(x) is achieved.

2. Optimization Under Constraints with Solver in Excel

In the previous example, there was no constraint. Here, we’ll see the usage of constraints in the solver for optimization in Exel.

In this case, we have 2300 cm 2 of metal sheet in our hand. From this, we have to make a cylinder whose Height should be double its Radius . We have to optimize the system so that the Volume gets maximized.

• The given data and constraints are present in cells C9 , and C10 .

• To determine the Volume , we input the formula below in cell C7 . The mathematical form of the formula is, V = πr 2 h .

Here, the PI function means π which value is 3.1416 . C4 and C5 cells denote the values of height and radius respectively.

• In cell C8 , we insert the formula of the total surface area of a cylindrical object. The mathematical form of the formula is, A = 2πr(h+r) . Here A serves as the total surface area.

In the Solver Parameters dialog box,

• Select cell C7 in the Set Objective box and choose Min in the To section.
• In the By Changing Variable Cells box, select cells in the C4:C5 range. Then, click on Add to add constraints.

• In the Add Constraint dialog box, select C4 as Cell Reference , select the Equal sign, and write down the following in the Constraint box.
• Then, click on Add for adding more constraints.

• Again, select C8 as Cell Reference , select the Equal sign, and select cell C9 in the Constraint box. Lastly, click OK .

• You can find the added constraints in the Subject to the Constraints box in the following image.
• Select GRG Nonlinear in the Select a Solving Method section and click Solve .

• In the Solver Results dialog box, check the Keep Solver Solution option and click OK .

Your expected result is here. The maximum achievable Volume with this area of the metal sheet is visible in cell C7 and its regarding Height and Radius are also here.

3. Maximizing Profit by Optimization in Product Mix

Here, we have the profit for each product of 4 types in the C6:F6 range. The required amount of material for each product type is available in the C9:F12 range. And the available amount of each material is here under the Available Resource column ( Column H ).

Now, we’ll calculate the quantity of each product to be produced using the given resources. And our prime objective is to maximize the profit from this production.

• To calculate the Total Profit in cell G6 , enter the formula below.

The SUMPRODUCT function returns the sum of the products of the corresponding values from all the arrays.

• Then, enter the following formula in cell G9 to calculate the amount of used resources for each material available.

• Select cell G6 in the Set Objective box and choose Max in the To section.
• In the By Changing Variable Cells box, select cells in the C5:F5 range.
• The constraints are described in the image and we selected the Simplex LP method for solving.
• Click Solve .

Excel calculated the product quantity, used resources, and maximum total profit at a glance.

Read More: How to Calculate Optimal Product Mix in Excel

4. Profit Maximization in Retail Stores with Optimization

We have several products from a retail electronics store. There are cost/unit, inventory/unit, and profit/unit are available under columns D , E , and F . Also, the total capacity of inventory and the total capital (cash in hand) are given in the D11:D12 range.

Our aim is to maximize the total profit by using these resources.

• The total unit of products is calculated by the following formula in cell C9 .

The SUM function adds all the numbers in each range of cells.

• We calculated the total cost with the following formula in cell D9 .

Similarly , we calculated the total inventory and total profit in cells E9 and F9 .

• Select cell F9 in the Set Objective box and choose Max in the To section.
• In the By Changing Variable Cells box, select cells in the C5:C8 range.
• Constraints are: every product quantity should be equal to or greater than zero. So, it must be non-negative. The total cost of buying products should be less than the total capital (cash in hand). The total occupied inventory should be less than the total available inventory .
• We selected the Simplex LP method for solving.

So, we can see that, only by buying AC s he can make the most profit. But he has to expand his inventory as he could spend only one-third of his capital.

5. Logistics Cost Optimization with Excel Solver

In the C6:F7 range, we have the transportation fees for 4 different stores from 2 different storage houses. The available products in each storage are also available in the H11:H12 range. We can also find the demand for each store (retail store) in the C14:F14 range.

We should meet the demand of each store and make the logistics cost the bare minimum.

• The following formula in cell G11 determines the total shipped products from Storage 1 .
• A similar thing can be done for Storage 2 in cell G12 .

• For calculating the total received products of Store 1 from both storages, enter the following formula in cell C13 .
• Do the same thing for the 3 remaining stores in cells D13:F13 .

• Select cell D16 in the Set Objective box and choose Min in the To section.
• In the By Changing Variable Cells box, select cells in the C11:F12 range.
• Constraints are described in the following image and we selected the Simplex LP method for solving.

Our optimization in Excel is done.

Read More: How to Perform Route Optimization in Excel

6. Create Magic Square with Solver in Excel

What is Magic Square?

A magic square is like a puzzle that consists of a square grid. You can imagine it as a square box divided into smaller boxes or cells. Each cell in the grid contains a different number, usually a whole number.

The remarkable thing about a magic square is that if you add up the numbers in any row (going from left to right), any column (going from top to bottom), or either of the two main diagonals (the diagonal lines that go from one corner of the square to the opposite corner), the total will always be the same.

It’s like magic because the sum will always be equal no matter which row, column, or diagonal you choose.

How to Create Magic Square

Here, we opt to create a 4*4 magic square. So there would be 1 to 16 numbers in the 16 different cells. We calculated the sum of rows in the H6:H9 range. Also, there is the sum of columns in the C11:F11 range. And, the diagonal sums are in the C13:C14 range.

For a 4*4 magic square, the sum for any columns or rows has to be,

Here, n represents the size of the magic square, which is the number of cells in each row or column.

In the Solver Parameters dialog box, we inserted the following constraints as in the image below.

• Click Solve.

Magically, Excel created the magic square. You can apply the same procedure for magic squares of any dimension.

Algorithms of Excel Solver

When you’re working with the Excel Solver and need to define a problem, you have the option to choose from different solving methods in the dropdown box called “ Select a Solving Method “. Let me explain what each of these methods does:

GRG Nonlinear: This method utilizes the Generalized Reduced Gradient (GRG) Nonlinear algorithm. It is specifically designed for tackling problems that involve smooth nonlinear functions. In other words, if your problem includes at least one constraint that is a smooth nonlinear function of the decision variables, this method can be useful.

LP Simplex: The LP Simplex method is based on the Simplex algorithm, which was developed by an American mathematician named George Dantzig . It is employed for solving Linear Programming (LP) problems. Linear Programming deals with mathematical models where the requirements can be described by linear relationships. These models usually consist of a single objective represented by a linear equation that needs to be either maximized or minimized.

Evolutionary: The Evolutionary method is suited for non-smooth problems, which are generally the most challenging type of optimization problems to solve. Non-smooth problems often involve functions that are not smooth or even discontinuous. In such cases, determining the direction in which a function is increasing or decreasing becomes difficult. The Evolutionary method is tailored to handle these complex optimization problems.

By selecting the appropriate solving method based on the nature of your problem, you can leverage the power of Excel Solver to find optimal solutions efficiently.

Things to Remember

• Enable the Solver Add-in before starting work on optimization.
• Make sure to select the appropriate solving method for your problem. Otherwise, the result may differ.
• If you find that the Excel Solver has been working on a specific problem for a while and you want to stop it, you can simply press the Esc key to interrupt the process.

Frequently Asked Questions

1. What is the objective function in optimization?

The objective function in optimization is a mathematical expression that defines the quantity you want to maximize or minimize. It represents the goal you are trying to achieve.

2. What are the constraints in optimization?

Constraints are the limitations or restrictions imposed on the decision variables in an optimization problem. They define the feasible region or the set of valid solutions.

3. Can Excel Solver handle large-scale optimization problems?

While Excel Solver is capable of solving moderately large optimization problems, its performance may be limited when dealing with very large-scale or complex problems. In such cases, specialized optimization software or programming languages designed for optimization, like MATLAB or Python, may be more suitable.

In conclusion, we tried to cover the definition of optimization in Excel, what is solver, and how to enable it in Excel. Also, we showed 6 different examples of using a solver in an optimization problem in Excel.

Excel’s optimization capabilities extend beyond Solver, with additional features like Goal Seek, Scenario Manager, and Data Tables. These tools allow users to analyze sensitivity, perform what-if analysis, and understand how changes in variables impact outcomes.

So, that’s all for today. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Optimization in Excel: Knowledge Hub

• Linear Optimization Model in Excel
• How to Make Price Optimization Models in Excel 
• How to Solve Network Optimization Model in Excel
• Excel Optimization with Constraints 
• Schedule Optimization in Excel
• How to Perform Multi-Objective Optimization with Excel Solver
• How to Optimize Multiple Variables in Excel
• Mean Variance Optimization in Excel

<< Go Back to  Solver in Excel  |  Learn Excel

What is ExcelDemy?

Tags: Solver in Excel

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Get instant live expert help with Excel or Google Sheets

“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

• CONDITIONAL FORMATTING
• PIVOT TABLES
• LIVE EXCEL HELP
• ONLINE EXCEL HELP
• EXCEL HELP CHAT
• FREE EXCEL HELP
• EXCEL EXPERT HELP
• BOX AND WHISKER
• CONCATENATE
• TIME AND DATE
• DATA VALIDATION
• DATE SERIES
• MAX AND MIN
• ENGINEERING
• STATISTICAL

Here are some problems that our users have asked and received explanations on

• Skip to primary navigation
• Skip to main content
• Skip to footer

Supply Chain Management Design & Simulation Online

How to Solve Transportation Problems Using Excel Solver

May 17, 2021 By Mohammed BOUALAM

Transportation costs are a significant component of the overall supply chain costs. Therefore, supply chain and logistics managers must take advantage of transportation’s potential in terms of opportunities for cost reduction. In this article, we will be exploring one of the fundamental cases of market and supply allocation; it’s called the Transportation problem .

The goal is to satisfy demand, which means being responsive, while also being efficient, which means having the lowest transportation cost. Allocating supply sources to facilities properly has a considerable impact on your supply chain’s financial and operating performance. By doing it right, you will be managing production and inventory effectively, speeding processes, and improving customer service.

The Transportation Problem

The transportation problem is one of the subclasses of a linear programming problem in which the objective is to transport products stored in a facility (e.g. a warehouse) to different destinations or markets in such a way as to minimize total transportation cost while satisfying all the supply and demand constraints.

Here is a list of information needed to solve a transportation problem:

• Product demand at each destination facility.
• Facility locations and distances between each source and destination facility.
• Cost per kilometer traveled.
• The maximum storage capacity of each source facility.

Below is the supply chain network we are going to use. It’s a simple network with two stages: there are three Distribution Centers, and the other eight facilities are Stores. Facilities are defined and their icons are put on the map using the SCM Globe supply chain modeling and simulation app. In the screenshot below you can see the facilities and the network of possible roads to use in delivering products from one facility to another.

We are glad to provide a  free evaluation account  to instructors, students and supply chain professionals interested in exploring SCM Globe simulations — click here to request an account —  Get Your Free Trial Demo

Moving to Excel Solver

Now that we have defined the problem and the network model, it’s time to move to Excel to enter the data and make our analysis.

The first step in our process is to enter the data we need from the supply chain model in an organized manner. Click on each facility in the SCM Globe supply chain model to see information for product demand, storage capacity, etc. Start by entering each store’s demand; that gives us a total demand of 1190 units in this case. Then enter the unit storage capacity for each distribution center. That gives us a total capacity of 2100 units. We can easily see we have enough capacity to cover market demand.

The second step is to enter the distances between all sources and destinations (between DCs and Stores). To find these distances, create routes between each DC and each of the stores. At each DC define a truck and create routes for it to each store (you don’t need to save these routes, just create them to get distances to the stores). When you do this the route distances shown will be round trip distances, so divide these distances in half . The cost per Km is $ 0.9 which is a default value in the software for large trucks. You can change this default value to more accurately reflect current actual costs per Km based on your research. Later, after the solver shows which are the best routes to use, then you will come back to the supply chain model to create and save those routes.

Next, create the changing variables in your spreadsheet template. Remember, our goal is to find delivery quantities from DCs to stores that minimize transportation costs while still meeting store demand. Therefore, the changing variables in our case are the number of units moving on each route.

The decision variables that will be changing are colored in yellow. We can make the value in the yellow matrix zeros, but to make sure that our rows and columns sums are correct, we made them equal to one. The values on the right side of the matrix are the total quantity delivered to each store, and the values below the matrix are the total quantity that each DC will transfer. We also copied the DC’s and capacity matrix, and we pasted its Transpose. We did that so we can add quickly and easily the capacity constraint in Solver.

B efore creating and running our solver model, we should define the total cost formula. Here, we have two options: 

• The total transportation cost can be equal to the cost per Km multiplied by the distance of each route and then multiplied by the number of deliveries needed since vehicles have a maximum carry volume.
• If we have the cost per Km per unit, then the total cost will be equal to the cost per km per unit, multiplied by the distance of each route, and then multiplied by the number of units sent on those routes. 

In our case, we only have the cost per Km traveled, and so we will use the first option. To know how many trucks we need to deliver the quantity incurred from one DC to a Store, we have to divide this quantity by the maximum carry volume of our trucks and round that number up ( The ROUNDUP Function in Excel ). In our example, we suppose that one large truck has a maximum carry volume of 110 units. The number of deliveries needed will be then equal to “ ROUNDUP(I15:K22/110;0)” . And the total transportation cost formula is “ SUMPRODUCT(C15:E22;ROUNDUP(I15:K22/110;0))*C24 ”.

If you have the cost per Km per unit, then the formula will be basically “ SUMPRODUCT(C15:E22;I15:K22)*C24” . Something to keep in mind here is that, in this simple transportation problem, the cost per Km or the cost per Km per unit will not change the solution, but it only affects the total cost.

Creating and Running the Solver Model

Let’s define the objective function and the constraints of our model:

• The objective function : The objective of our model is to minimize the total cost.
• Constraints : First, we have demand constraints, therefore to meet demand, the total quantity delivered to each store must be equal to the demand of that store. Second, the quantity delivered from each DC must not exceed its unit capacity. And third, the decisions variable, which are the quantities delivered, must be an integer and greater than or equal to zero.

Now, let’s see how we can apply this to the Excel Solver add-in. First, go to the “Data” tab, and click on the “Solver” button that can be found on the right side. [If you have not installed the Solver yet visit this link to learn how to install it: https://www.solver.com/excel-solver-how-load-or-start-solver .] 

Once the solver tab opens, we have to set the objective. In our case, we select the “I25” cell in green; this is our total cost. The variables that we are changing are grouped in the yellow matrix, and so we select this matrix in the second block. 

Concerning the constraints, we have to click on the “Add” button. The three constraints created are shown in the figures below. After adding all these constraints, we have first to make unconstrained variables non-negative by selecting the checkbox. By respecting these installed constraints we can run our Solver model by selecting the Simplex LP (a solving method since our model is linear).

Our model is now ready, so click the “Solve” button. We can see that the decision variables and the total cost are changing. The result is a preliminary optimal solution for our transportation problem.

Interpretation of Results

We can verify results by seeing if we meet all demands and if capacity constraints are respected.

In this example, we got a total cost of $ 3,991.20, which is acceptable, but we notice that the only warehouse that will be using its max storage capacity is the DC Nbr 1. DC Nbr 3 is using 60% of its max storage capacity, and DC Nbr 2 is only using 10%. Hence the question: Do we need to open all three distribution centers for the next period?

One can say that by using only two facilities, the costs of operating facilities will decrease, but the transportation costs may increase. To find the optimal solution, we have to compare the total cost of two different scenarios. Which scenario offers the lowest total cost while still meeting store demand? In the first scenario, the company opens and uses all its DCs, paying all its fixed costs. In the second scenario, the company operates only two of its DCs while closing the third one.

Make copies of your original SCM Globe supply chain model and change them to reflect these two potential supply chain designs. Run those models in simulation to see how they work, and generate simulation data to create Profit & Loss Reports and KPIs . Compare the simulations and their performance reports. Get review and input from relevant parties, and pick the supply chain design that reviewers feel best meets the needs of the company in this situation.

Supply chain management is both a science and an art . In addition to numeric calculations, the role of professional judgment is also important as it can find growth opportunities, while numeric calculations find ways to reduce costs. Simulations help people explore different ideas and find new opportunities. Simulations bring together numeric calculations and professional judgment.

Situations change from month to month. This month’s optimal solution can quickly become next month’s big mistake. Companies plot their course each month by combining numeric calculations with professional judgment to navigate successfully through a constantly changing world.

[ See more about using the solver for supply chain route selection and scheduling in our blog post “ Manage Your Transportation by Solving The Vehicle Routing Problem “ ]

Special thanks to Mr. Curtis Frye for the quality of his course titled: “ Excel Supply Chain Analysis: Solving Transportation Problems ” on LinkedIn Learning. We based our Excel demonstration on his way of organizing the spreadsheet template.