# How to use solver for this case?

#### 1234_a

##### New Member
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 1 exercise 2 exercise 3 exercise 4 exercise 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
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
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
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
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_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
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_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.

Replies
1
Views
72
Replies
2
Views
90
Replies
1
Views
233
Replies
0
Views
123
Replies
4
Views
186

1,132,902
Messages
5,655,879
Members
418,249
Latest member

### 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.

### Which adblocker are you using?

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

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