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