assignment problem using excel solver

#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

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.

• 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

404 Not found

• 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 NPER Function: Time Period Calculation

Excel offset function: business applications, excel or function: versatile logic evaluation, 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 Rolling 6 Months Average: Data Analysis Made Easy

#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

• WordPress.org
• Documentation
• Learn WordPress

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

• What is My IP Address?

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

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

How to Use Excel Solver to Determine Which Projects Should Be Undertaken?

This tutorial will demonstrate how to use Excel Solver to determine which projects should be undertaken. Companies need to know which projects should undertake in order to produce the greatest NPV (net present value) when there are very few resources. Applying Excel Solver can help solve this kind of problem. We often apply it directly to predict the outcome or help in decision-making. So, it is important to learn how to use Excel solver to determine which projects should be undertaken.

What Is an Excel Solver?

Excel Solver is used in many different algorithms for finding the solution to different complex linear and non-linear problems. The methods of Excel Solver are.

GRG Nonlinear : The full form of this method is the Generalized Reduced Gradient Nonlinear method. We will use this method when simple nonlinear problems where the data will contain a minimum of one constraint.

LP Simplex : We will use Excel Solver for Linear Programming problems when linear relationships occur. With the linear connection with the data, linear programming tends to achieve the desired goal such as minimum loss, maximum profit, etc.

Evolutionary : Among all the types, this is the most difficult where you can not determine the characteristics of the function. The system of this kind of function can increase or decrease at any time. This is because the functions are either discontinuous or nonsmooth.

How to Enable Excel Solver

Normally, Excel Solver isn’t available in the Data tab. We have to add it manually to our menu tab. We can add and enable Excel by following the below process:

• First, go to the File  option.

• Then, go to More… > Options .

• Next, go to Add-ins > Solver Add-ins > OK  options.

• After that, in the Developer tab select the Excel Add-ins  option.

• Afterward, tick the Solver Add-in option and press OK .

• Finally, go to Data > Solver options to open the Solver Parameters dialog box.

How to Use Excel Solver to Determine Which Projects Should Be Undertaken: 3 Cases

We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to use Excel Solver to determine which projects should be undertaken on your own. The steps are:

1. Maximizing NPV to Determine Which Projects Should Be Undertaken

In this case, our goal is to use an Excel solver to determine which projects should be undertaken by maximizing NPV in Excel. In this case, the project that has maximum NPV will always undertake the project. The steps of this process are.

• First, arrange a dataset like the below image. In this case, we have Project No in cell B4 , NPV in cell C4, Year 1 in cell D4, and Year 2  in cell E4 .

• Next, add more cells like Objective, By Changing Cells, and Needed We will use these three parameters to use the solver option correctly.

• After that, go to Data>Solver options to open the Solver Parameters dialog box. In the Solver Parameters dialog box, fulfill the Set Objective, To, By Changing Variable Cells, Subject to Constraints, and Select a Solving Method After that, press the Solve option to get the desired result.

• Afterward, you will get the Solver Results dialog box. Here, select the Keep Solver Solution and press OK .

• Finally, you will get the desired result like the following image.

Read More: How to Do Portfolio Optimization Using Excel Solver

2. Another Project Must be Undertaken If One Project is Ended

Suppose that if project 4 is undertaken, project 5 must be undertaken. The model, in this case, will be the same as that in case 1 except for a new constraint. Therefore, the worksheet will look the same as that in case 1 . We will describe the full process in the below steps.

• First, arrange a dataset like the below image.

• Second, go to Data > Solver options to open the Solver Parameters dialog box. In the Solver Parameters dialog box, fulfill the Set Objective, To, By Changing Variable Cells, Subject to Constraints, and Select a Solving Method After that, press the Solve option to get the desired result.

• Last, you will get the desired result like the following image.

Read More: How to Use Excel Solver for Linear Programming

3. Choosing the Number of Projects That Must be Undertaken

Now, suppose that you can do only three projects among those 9 projects. The model, in this case, will be the same as that in case 2 except for a new constraint. Therefore, the worksheet will look the same as that in case 2 . We will describe the full process in the below steps.

• To begin with, arrange a dataset like the below image.

• In addition, go to Data > Solver options to open the Solver Parameters dialog box. In the Solver Parameters dialog box, fulfill the Set Objective, To, By Changing Variable Cells, Subject to Constraints, and Select a Solving Method After that, press the Solve option to get the desired result.

Read More: Example with Excel Solver to Minimize Cost

Things to Remember

• Excel Solver isn’t normally added in the Data tab. So, at the very beginning, add it to your Data tab by following the above description.
• The most important thing is to focus while using the Solver Parameters dialog box. Any mistake in the dialog box will cause a different result in this case.
• All the main formulas must be checked properly before using the Solver If you have any problems with formulas or data then you have to correct them before using the Solver option.

Download Practice Workbook

You can download the practice workbook from here.

Henceforth, follow the above-described methods. Hopefully, these methods will help you learn to use Excel solver to determine which projects should be undertaken. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

Related Articles

• How to Use Excel Solver to Rate Sports Team
• Solving Sequencing Problems Using Excel Solver Solution
• Solving Transportation or Distribution Problems Using Excel Solver
• How to Assign Work Using Evolutionary Solver in Excel
• Resource Allocation in Excel
• How to Create Financial Planning Calculator in Excel
• Solving Equations in Excel

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

What is ExcelDemy?

Tags: Excel Solver Examples

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet... 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

Using Solver to schedule your workforce

Many businesses (such as banks, restaurants, and postal service companies) know what their labor requirements will be on different days of the week, and need a method to efficiently schedule their workforce. You can use Excel's Solver add-in to create a staffing schedule based on those requirements.

Schedule your workforce to meet labor demands (example)

The following example demonstrates how you can use Solver to calculate staffing requirements.

Contoso bank processes checks 7 days a week. The number of workers needed each day to process checks is shown in row 14 of the Excel worksheet shown below. For example, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. All bank employees work 5 consecutive days. What is the minimum number of employees the bank can have and still meet its labor requirements?

Start by identifying the objective cell, changing cells, and constraints for your Solver model.

Note:  The objective cell is called the target cell in Excel 2007.

Objective cell – Minimize the total number of employees.

Changing cells – Number of employees who start work (the first of five consecutive days) each day of the week. Each changing cell must be a non-negative integer.

Constraints – For each day of the week, the number of employees who are working must be greater than or equal to the number of employees required. ( Number of employees working ) >= ( Needed employees )

To set up the model, you need to track the number of employees working each day. Begin by entering trial values for the number of employees who start their five-day shift each day in the cell range A5:A11. For example, in A5, enter 1 to indicate that 1 employee begins work on Monday and works Monday through Friday. Enter each day’s required workers in the range C14:I14.

To track the number of employees working each day, enter a 1 or a 0 in each cell in the range C5:I11. The value 1 in a cell indicates that the employees who started working on the day designated in the cell’s row are working on the day associated with the cell’s column. For example, the 1 in cell G5 indicates that employees who started working on Monday are working on Friday; the 0 in cell H5 indicates that the employees who started working on Monday are not working on Saturday.

To compute the number of employees working each day, copy the formula = SUMPRODUCT($A$5:$A$11,C5:C11) from C12 to D12:I12. For example, in cell C12, this formula evaluates to = A5+A8+A9+A10 +A11 , which equals (Number starting on Monday)+ (Number starting on Thursday)+(Number starting on Friday)+(Number starting on Saturday)+ (Number starting on Sunday) . This total is the number of people working on Monday.

After computing the total number of employees in cell A3 with the formula = SUM(A5:A11) , you can enter your model in Solver as shown below.

In the objective cell (A3), you want to minimize the total number of employees. The constraint C12:I12> =C14:I14 ensures that the number of employees working each day is at least as large as the number needed for that day. The constraint A5:A11=integer ensures that the number of employees beginning work each day is an integer. To add this constraint,click Add in the Solver Parameters dialog box and enter the constraint in the Add Constraint dialog box (shown below).

You can also selected the options Assume Linear Model and Assume Non-Negative for the changing cells by clicking Options in the Solver Parameters dialog box and then selecting the check boxes in the Solver Options dialog box.

Click Solve . You'll see the optimal number of employees for each day.

In this example, a total of 20 employees is needed. One employee starts on Monday, three start on Tuesday, four start on Thursday, one starts on Friday, two start on Saturday, and nine start on Sunday.

Note that this model is linear because the objective cell is created by adding changing cells, and the constraint is created by comparing the result obtained by adding the product of each changing cell times a constant (either 1 or 0) to the required number of workers.

Top of Page

Need more help?

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

Load the Solver Add-in in Excel

Get Microsoft schedule templates

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.