Solving assignment problem with Excel
Here's how to solve assignment problems with Excel.
To formulate an assignment problem, these three questions must be answered.
- What are the decisions to be made? For this problem, we need Excel to know which person to assign to which task (Yes = 1, No = 0). For example, if we assign person 1 to task 1, cell C10 is equal to 1. Otherwise, cell C10 is equal to 0. (in yellow)
- What are the constraints on these decisions? Each person can perform only one task (Supply = 1). Each task only needs one person (Demand = 1). (in light blue)
- What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment. The objective is therefore to minimize this quantity. (in dark blue)
Let us name the following ranges:
| Beach name | Cells |
|---|---|
| Cost | C4: E6 |
| assignment | C10: E12 |
| PersonsAssigned | C14: E14 |
| Demand | C16: E16 |
| TasksAssigned | G10: G12 |
| Supply | I10: I12 |
| TotalCost | I16 |
And let's insert the following functions:
Solve the model
Let’s enter the solver parameters:
The optimal solution is:
