How to use solver for this case?

1234_a

New Member
Joined
Mar 11, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. 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​
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
927
Office Version
  1. 365
Platform
  1. 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
  1. 2016
Platform
  1. 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!
 

1234_a

New Member
Joined
Mar 11, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
927
Office Version
  1. 365
Platform
  1. 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
10,958
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
  1. 2016
Platform
  1. 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,132,902
Messages
5,655,879
Members
418,249
Latest member
JOYADA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top