+ Reply to Thread
Results 1 to 9 of 9

Difficulties Using Solver for Staff Schedule - Complicated Criteria Set

  1. #1
    Registered User
    Join Date
    08-12-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Difficulties Using Solver for Staff Schedule - Complicated Criteria Set

    Hi, I have a similar problem and tried to use opensolver but ran into issues. maybe the problem is too complicated for excel.

    I'm trying to create a staff schedule where:
    24/7 department
    12 hour shifts (day and night)
    max 3 consecutive nights
    max 4 consecutive days
    3 skill types that are random among staff
    staff are Full time (6 shifts per 2 weeks minimum and soft maximum), part time (4 shifts per 2 weeks min and 6 max) or casual (6 shift/ 2 weeks max)
    in model i have 15 employees (actual will be 50-60ish)
    each shift has 1 person fill it for one day
    weekends and holidays may not require certain shifts
    each shift may require a specific skill
    eventually need special rules for specific staff like less nights or no nights.


    Approach:
    I create a table with repeating dates for the column and the 2nd column i have every shift repeating
    several other columns to show whether a shift is required for that date (if holiday or weekend)
    then the row headers had every staff
    body would be 1 or 0 to show the person taking the shift
    constraints would be on separate sheets that use combinations of index/match and sumproducts to count skills, or shifts and check if they fit a rule.
    objective is to have 1 shift filled by one person for every day (sum all of the different between actual and target) and aim for 0

    Outcome:
    Solver was supposed to fill in the table and comply with the constraints but it always goes crazy and tries to schedule a bunch of people in one row.



    Any help or direction would be greatly appreciated.
    Attached Files Attached Files
    Last edited by AliGW; 07-03-2020 at 03:03 AM.

  2. #2
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Too many variable cells for Solver - Numbers that add up to a specific value

    Quote Originally Posted by icefire View Post
    Any help or direction would be greatly appreciated.
    Minimizing cell AC1, and adding a constraint so that AC1 >=0, the model can be solved locally (CBC engine).
    The setup requires a lot of time, but the solution is found almost immediatly.

    Not sure if this constraint is defined in the right range, though

    'Constraints(Days)'!$T$7:$W$7 >= 'Constraints(Days)'!$T$5

    HTH,

    Francesco
    Attached Files Attached Files
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Numbers to Add Up to a Specific Value

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    As you are new, I have moved your hijack and the response received to this new thread.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Too many variable cells for Solver - Numbers that add up to a specific value

    Quote Originally Posted by Hydraulics View Post
    The setup requires a lot of time, but the solution is found almost immediatly.
    "A lot" means 15 minutes, more or less.

    However, you would pay that price even if you were using an online server.

    HTH,

    Francesco

  5. #5
    Registered User
    Join Date
    08-12-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Numbers to Add Up to a Specific Value

    Quote Originally Posted by AliGW View Post
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.



    As you are new, I have moved your hijack and the response received to this new thread.
    Sorry about that. Sure sign of a newb.

  6. #6
    Registered User
    Join Date
    08-12-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Too many variable cells for Solver - Numbers that add up to a specific value

    Thank you very much. not sure what is going on but playing around with it some more seems to get some decent results.

    now to pile on more constraints and requirements
    Last edited by icefire; 07-04-2020 at 01:05 AM.

  7. #7
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    41

    Re: Too many variable cells for Solver - Numbers that add up to a specific value

    I also encountered this "setup took a lot of time" problem. Please see the reply from Open Solver auther:

    We have to re-calculate the spreadsheet once for every decision cell (after changing that decision cell by +1) to extract the model from the spreadsheet for CBC. This can be slow, and is made worse as VBA can slow down over time for reasons (to do with memory?) we don’t understand. Excel seems to use just one core when doing these re-calculations; it is not something we can influence. You can speed this up by making sure the workbook contains only the model spreadsheet, with no graphs or other calculations. SolverStudio with PuLP and an open source solver will be much faster as no spreadsheet recalculations are required. Hope this helps. Andrew
    Last edited by AliGW; 08-06-2020 at 02:18 AM. Reason: Please don't quote unnecessarily!

  8. #8
    Registered User
    Join Date
    08-12-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Difficulties Using Solver for Staff Schedule - Complicated Criteria Set

    So I plugged in a full version with all the variables and it took 3 days to run. In the end, I didn't update the variables properly and decided not to try again given how long it took. I've been going down a rabbit hole ever since exploring Studio solver, programming with Julia, considered learning python or R or even Java since there is an existing package called OptaWeb for scheduling that requires modification to suit my needs. Optaweb is the most polished but Julia and StudioSolver seem the easiest to learn. I've been majorly slacking given the number of constraints I need to build but am slowly working away at this route.

  9. #9
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Difficulties Using Solver for Staff Schedule - Complicated Criteria Set

    Quote Originally Posted by icefire View Post
    I've been going down a rabbit hole ever since exploring Studio solver, programming with Julia, considered learning python or R or even Java since there is an existing package called OptaWeb for scheduling that requires modification to suit my needs.
    If you are willing to invest some time in learning Python, Pyomo or mip-python are 2 packages that you should test. The syntax follows the lines of a mathematical programming language, therefore it may be somewhat hard to grasp at the start, but there are many examples to be found online for both packages.

    An added bonus is the possibility to use a commercial solver (Gurobi, CPLEX), if you ever have the chance to put your hands on one of them.

    In a warehouse location problem I have recently built (100k variables, 13k constraints), OpenSolver needed more or less 4 hours to build the model, the Python version was below 1 minute.

    Finally, scheduling problems are often coded using constraint programming. If you haven't yet tried them, have a look at OR-Tools, and maybe miniZinc.

    HTH,

    Francesco

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Match specific numbers of two different files and do sum of values ahead of that numbers
    By keshavtale in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-22-2020, 02:11 PM
  2. [SOLVED] Extract the text and numbers basis on specific numbers included
    By Neilesh Kumar in forum Excel General
    Replies: 6
    Last Post: 08-26-2018, 01:34 PM
  3. [SOLVED] Function possible for Using specific number to count other specific numbers total ?
    By Karnik in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-02-2015, 03:35 AM
  4. Replies: 6
    Last Post: 02-27-2014, 07:04 PM
  5. Replies: 1
    Last Post: 12-28-2013, 11:46 PM
  6. Replies: 15
    Last Post: 10-11-2009, 11:46 AM
  7. [SOLVED] Highlight a row if a specific cell is specific numbers/words
    By sea0221 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1