How to use solver for this case?

1234_a

New Member
Joined
Mar 11, 2020
Messages
9
Office Version
2016
Platform
Windows
The exercise is:
There are 5 employees in your small business who can perform any of the 5 different tasks that your company has. The following table shows how many hours of working time are required for each employee to perform each obligation.
Your job is to split these responsibilities so that each of the employees carries out exactly one obligation and each obligation is fulfilled exactly once and at the same time the total time in hours to complete all the work is the least possible.
exercise 1exercise 2exercise 3exercise 4exercise 5
1.employee
8​
12​
21​
9​
10​
2. employee
5​
18​
19​
10​
12​
3.employee
9​
17​
18​
9​
13​
4. employee
7​
15​
17​
11​
9​
5.employee
6​
19​
21​
8​
12​
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
552
Office Version
365
Platform
Windows
I think it is an Assignment Probelm. Will get back with the probelm after understanding how assignment probelms can be solved
 

1234_a

New Member
Joined
Mar 11, 2020
Messages
9
Office Version
2016
Platform
Windows
I think it is an Assignment Probelm. Will get back with the probelm after understanding how assignment probelms can be solved
Thank you. I will wait for your response!
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
552
Office Version
365
Platform
Windows
I have gone through similar kind of Assignment Probelm but i am not able to devise a formula to calculate the optimum result. Hopefully some one with VBA knowledge could help
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
When you say this is an exercise, is this for a class, or seminar or something? Generally speaking, a lot of the helpers here don't like to give answers to class questions since the point is to learn, and if we just give an answer, that defeats the purpose. We are willing to give hints and pointers though. And when you say "Solver", do you mean that this is the tool that the exercise requires?

Consider this layout:

Book1
ABCDEFGHIJKL
1exercise 1exercise 2exercise 3exercise 4exercise 5TaskAll used?Hours needed
21.employee81221910118
32. employee518191012218
43.employee91718913318
54. employee71517119411
65.employee61921812512
7
8Sum:67
Sheet4
Cell Formulas
RangeFormula
J2J2=PRODUCT(--(COUNTIF(H2:H6,{1,2,3,4,5})=1))
L2:L6L2=INDEX(B2:F2,H2)
L8L8=SUM(L2:L6)
Named Ranges
NameRefers ToCells
solver_adj=Sheet4!$H$2:$H$6J2, L2
solver_lhs1=Sheet4!$H$2:$H$6J2, L2
solver_lhs2=Sheet4!$H$2:$H$6J2, L2
solver_lhs3=Sheet4!$H$2:$H$6J2, L2


A1:F6 is your table. H2:H6 is a column saying which task is assigned to which employee. They should be a number from 1 to 5, and each should be used exactly once. The formula in J2 tells you if every task is used exactly once. Then the L2:L6 column is a simple lookup of the number of hours for that task, and L8 is the total hours. This now has everything you need to set up a Solver problem. Minimize cell L8, varying cells H2:H6 subject to the constraints that H2:H6 must be 1-5, and J2 must be 1. You'll also probably need to select the Evolutionary solving method. When you get it all set up, it should run through several thousand cases (3125 probably), and come up with an answer in the H2:H6 column. The L8 total should be a bit less that what you see here.

This should answer your question, but there are still some flaws in it. For example, there are 5! = 120 possible ways to assign the tasks. Seems like there should be a way to check only those ways, and not all 3125 ways that the Solver checks. Also, you are dependent on the Solver method. Are you sure that it really gives you the best answer? After all, 2 of the 3 methods don't work. If you know VBA, it'd be easy to write a macro that exhaustively checks all 120 ways and gets the best answer. But what if you have 100 people and 200 tasks? An exhaustive macro could take years to run. There are algorithms that work in much faster ways.

Anyway, here are some ideas, and some things to think about. Good luck!
 

1234_a

New Member
Joined
Mar 11, 2020
Messages
9
Office Version
2016
Platform
Windows
When you say this is an exercise, is this for a class, or seminar or something? Generally speaking, a lot of the helpers here don't like to give answers to class questions since the point is to learn, and if we just give an answer, that defeats the purpose. We are willing to give hints and pointers though. And when you say "Solver", do you mean that this is the tool that the exercise requires?

Consider this layout:

Book1
ABCDEFGHIJKL
1exercise 1exercise 2exercise 3exercise 4exercise 5TaskAll used?Hours needed
21.employee81221910118
32. employee518191012218
43.employee91718913318
54. employee71517119411
65.employee61921812512
7
8Sum:67
Sheet4
Cell Formulas
RangeFormula
J2J2=PRODUCT(--(COUNTIF(H2:H6,{1,2,3,4,5})=1))
L2:L6L2=INDEX(B2:F2,H2)
L8L8=SUM(L2:L6)
Named Ranges
NameRefers ToCells
solver_adj=Sheet4!$H$2:$H$6J2, L2
solver_lhs1=Sheet4!$H$2:$H$6J2, L2
solver_lhs2=Sheet4!$H$2:$H$6J2, L2
solver_lhs3=Sheet4!$H$2:$H$6J2, L2


A1:F6 is your table. H2:H6 is a column saying which task is assigned to which employee. They should be a number from 1 to 5, and each should be used exactly once. The formula in J2 tells you if every task is used exactly once. Then the L2:L6 column is a simple lookup of the number of hours for that task, and L8 is the total hours. This now has everything you need to set up a Solver problem. Minimize cell L8, varying cells H2:H6 subject to the constraints that H2:H6 must be 1-5, and J2 must be 1. You'll also probably need to select the Evolutionary solving method. When you get it all set up, it should run through several thousand cases (3125 probably), and come up with an answer in the H2:H6 column. The L8 total should be a bit less that what you see here.

This should answer your question, but there are still some flaws in it. For example, there are 5! = 120 possible ways to assign the tasks. Seems like there should be a way to check only those ways, and not all 3125 ways that the Solver checks. Also, you are dependent on the Solver method. Are you sure that it really gives you the best answer? After all, 2 of the 3 methods don't work. If you know VBA, it'd be easy to write a macro that exhaustively checks all 120 ways and gets the best answer. But what if you have 100 people and 200 tasks? An exhaustive macro could take years to run. There are algorithms that work in much faster ways.

Anyway, here are some ideas, and some things to think about. Good luck!
Thank you! After your explanation I finally understood what I need to do.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,785
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top