# Even distribution of work contents with different frequencies

#### ccyilmaz

##### New Member
Hello everyone,

Since I'm struggling with an excel problem for long i decided to ask pro's about their opinions and suggestions. Any help is highly appreciated.

I try to organize frequent tasks so that each week gets similar share of the whole bunch. The year is divided in 52 weeks and there are in total 80 tasks with their own frequencies to be carried out. For instance a certain control needs to be done 8 times a year in equal intervals. The frequencies differ from every week to once a year. They also have their own durations variing from 0,5 hour to 8 hours. The model i try to build will be optimizing the personnel capacity need each week. The deviation of total duration for each week should not be big from each other.

What i did is:

I created a matrix with weeks in columns and tasks in rows. After the 52. week i added a column which returns the total necessary time for each task for the whole year by multiplying the frequency with the single duration. By using solver i tried to minimize the standard deviation of the sum of each weeks total duration of tasks. However i couldn't manage to make an optimization that way.

Anybody having a clue how i can make the optimization?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### oldbrewer

##### Well-known Member
a once a year task MIGHT be carried out in december and then january but that would be silly. Are the task durations really important since a year is 8760 hours. Do you work say 8 to 6 or 24 hours per day. I would tend to sort out the tasks that consume the biggest hours first...

#### oldbrewer

##### Well-known Member
 TASK FREQ HOURS freq*hours TASK FREQ HOURS freq*hours every 1 2 3 4 5 6 7 8 T1 4 4 16 T18 8 5 40 46 11/02/2016 28/03/2016 13/05/2016 28/06/2016 13/08/2016 28/09/2016 13/11/2016 29/12/2016 T2 6 6 36 T2 6 6 36 61 26/02/2016 27/04/2016 27/06/2016 27/08/2016 27/10/2016 27/12/2016 T3 3 7 21 T4 8 4 32 46 11/02/2016 28/03/2016 13/05/2016 28/06/2016 13/08/2016 28/09/2016 13/11/2016 29/12/2016 T4 8 4 32 T12 4 7 28 91 27/03/2016 26/06/2016 25/09/2016 25/12/2016 T5 4 6 24 T5 4 6 24 91 27/03/2016 26/06/2016 25/09/2016 25/12/2016 T6 2 3 6 T3 3 7 21 122 27/04/2016 27/08/2016 27/12/2016 T7 1 8 8 T8 4 5 20 91 27/03/2016 26/06/2016 25/09/2016 25/12/2016 T8 4 5 20 T15 4 5 20 91 27/03/2016 26/06/2016 25/09/2016 25/12/2016 T9 6 1 6 T10 3 6 18 122 27/04/2016 27/08/2016 27/12/2016 T10 3 6 18 T1 4 4 16 91 27/03/2016 26/06/2016 25/09/2016 25/12/2016 T11 8 1 8 T17 3 4 12 122 27/04/2016 27/08/2016 27/12/2016 T12 4 7 28 T20 2 6 12 183 2 27/06/2016 27/12/2016 T13 2 2 4 T7 1 8 8 365 1 26/12/2016 T14 1 6 6 T11 8 1 8 46 11/02/2016 28/03/2016 13/05/2016 28/06/2016 13/08/2016 28/09/2016 13/11/2016 29/12/2016 T15 4 5 20 T19 4 2 8 91 27/03/2016 26/06/2016 25/09/2016 25/12/2016 T16 6 1 6 T6 2 3 6 183 2 27/06/2016 27/12/2016 T17 3 4 12 T9 6 1 6 61 26/02/2016 27/04/2016 27/06/2016 27/08/2016 27/10/2016 27/12/2016 T18 8 5 40 T14 1 6 6 365 1 26/12/2016 T19 4 2 8 T16 6 1 6 61 26/02/2016 27/04/2016 27/06/2016 27/08/2016 27/10/2016 27/12/2016 T20 2 6 12 T13 2 2 4 183 2 27/06/2016 27/12/2016 01/01/2016 easy to set up something like this plot on a scatter chart and tinker with it…. eg the 1 2 and 3 frequencies can fill gaps after setting the bigger ones second table to the right is clearly first table sorted

<tbody>
</tbody>

Last edited:

#### ccyilmaz

##### New Member
Dear oldbrewer,

thanks for the suggestion. In this solution i am not sure if i get the same work load for each week.

I prepared following excel table showing a full matrix of tasks distributed throughout the whole year. My problem here is that the work load for each week is so much variing that in some weeks i have not enough manpower to complete them and in some weeks i need only 0,5 hour manpower capacity. The solution i seek should be arranging the tasks so that i get (in best case 1003 hours / 52 weeks = 19,3 hours) a not so much variing manpower need btw. the weeks.

 weeks TASK How many times annually a task should be done? Duration of each task Total time needed annually to complete the task 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 1 13 24 312 24,0 24,0 24,0 24,0 24,0 24,0 24,0 24,0 24,0 24,0 24,0 24,0 24,0 2 2 80 160 80,0 80,0 3 7 8 56 8,0 8,0 8,0 8,0 8,0 8,0 8,0 4 26 2 52 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 5 26 2 52 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 2,0 6 13 4 52 4,0 4,0 4,0 4,0 4,0 4,0 4,0 4,0 4,0 4,0 4,0 4,0 4,0 7 52 0,5 26 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 8 3 8 24 8,0 8,0 8,0 9 2 10 20 10,0 10,0 10 13 1,5 20 1,5 1,5 1,5 1,5 1,5 1,5 1,5 1,5 1,5 1,5 1,5 1,5 1,5 11 2 8 16 8,0 8,0 12 4 4 16 4,0 4,0 4,0 4,0 13 4 4 16 4,0 4,0 4,0 4,0 14 1 16 16 16,0 15 2 8 16 8,0 8,0 16 7 2 14 2,0 2,0 2,0 2,0 2,0 2,0 2,0 17 13 1 13 1,0 1,0 1,0 1,0 1,0 1,0 1,0 1,0 1,0 1,0 1,0 1,0 1,0 18 4 2,5 10 2,5 2,5 2,5 2,5 19 4 2 8 2,0 2,0 2,0 2,0 20 2 4 8 4,0 4,0 21 2 4 8 4,0 4,0 22 1 8 8 8,0 23 7 1 7 1,0 1,0 1,0 1,0 1,0 1,0 1,0 24 7 1 7 1,0 1,0 1,0 1,0 1,0 1,0 1,0 25 7 1 7 1,0 1,0 1,0 1,0 1,0 1,0 1,0 26 7 1 7 1,0 1,0 1,0 1,0 1,0 1,0 1,0 27 13 0,5 7 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 28 13 0,5 7 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 29 13 0,5 7 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 0,5 30 2 3 6 3,0 3,0 31 2 2,5 5 2,5 2,5 32 2 2,5 5 2,5 2,5 33 4 1 4 1,0 1,0 1,0 1,0 34 7 0,5 4 0,5 0,5 0,5 0,5 0,5 0,5 0,5 35 2 1 2 1,0 1,0 36 4 0,5 2 0,5 0,5 0,5 0,5 37 4 0,5 2 0,5 0,5 0,5 0,5 38 3 0,5 2 0,5 39 2 0,5 1 0,5 0,5 0,5 40 1 1 1 1,0 1003 Weekly needed man hours 223 1 5 1 37 1 5 1 51 1 5 1 37 15 5 1 51 9 5 1 37 1 5 1 51 1 142 1 37 1 5 1 51 1 5 9 37 1 5 15 51 1 5 1 37 1 5 1 51 1 5 1

<tbody>
</tbody><colgroup><col><col><col><col><col span="52"></colgroup>

Replies
0
Views
590
Replies
3
Views
1K
Replies
0
Views
177
Replies
3
Views
454
Replies
0
Views
367

1,195,939
Messages
6,012,425
Members
441,698
Latest member
DaveTeo

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