Apr 23 2020 03:32 PM - edited Apr 23 2020 03:37 PM
Hey All,
I’m not sure where to begin but I’d like to try and tackle some ‘optimisation’ in excel? I’m not unfamiliar with the concept but have never had to implement it.
The challenge is;
Every day I need to assign/roster desks to call centre staff. I have a list of people working, what times their shifts are, what ‘zone’ of the office they need to sit in, and a list of desks for that zone
My considerations/constraints are;
At the moment I am doing this ‘manually’. I have a whole lot of formulas to check that the above are taken in to consideration, but ultimately its me ‘assigning’ the desk and checking at the end that all the considerations are still met.
The way I’m thinking about it at the moment, I could assign a table of costs to the above constraints. E.g. desk preference is met, cost of zero otherwise cost of 100, etc. There would be a bit of set up and I haven’t put too much thought in to it but I reckon I could generate some cost tables and return cost for a desk selection for a person based on the above.
Could I use excels solver to help with this with the goal being to minimise total ‘cost’ and if so a helping hand/point in the right direction would be really appreciated, or is there a better or different method I should try?
Thanks for your help
Dec 18 2022 08:42 PM
@ScottAshby any luck with this? I'm running into a similar scenario
Dec 18 2022 09:59 PM - edited Dec 18 2022 10:00 PM
@cortes272 blast from the past, I forgot about this! I never got anywhere with the excel solver. There was a more complex solver add on but still no luck.
Knowing what I do now, I think an approach would have been to try and formulate an lp file for my problem, and then run it through a solving engine like neos-server or HiGHS.
At the time I might have been happy with a halfway solution like some VBA code and loops to assign preferences, then fill up the desks by zone until it couldnt any more and I'd deal with the leftovers manually.
Good luck with your problem