#Milp solver for excel download
If you are interested in that though, I definitely encourage you to download the MATLAB files for this example and check it out for yourself. My aim is more to show an application of MILP, rather than discussing the intricate details of problem formulation.
There is a bit of machinery involved in generating the appropriate matrices in this particular case, and it would be a bit dense to go through all the details in this blog post. The specifics of this are covered thoroughly in the documentation. Generating a MILP formulation of a particular problem involves expressing the minimization objective and constraints using linear equations, and these are typically written using matrix notation. Generate the f, A, and b matrices based on the the constraints and objectives Late at night, only 1 or 2 employees are needed, while during peak hours in the morning to afternoon, we may need as many as 9 employees on duty. Required staffing (hourly from 0:00 - 24:00): requirements = xlsread(filename,2) % Second sheet has minimum staff requirementsĭisp( 'Required staffing (hourly from 0:00 - 24:00): ') I don't need this to be table, I just want to import it as a numeric array, so xlsread will work just fine. We can also see the required staffing requirements for each hour of the 24-hour day. The variable staffTable has a list of each employee, along with the minimum hours they must work (if they are called in), maximum hours they may work, their hourly wage, and any limits on availability if there are any.įor example, the first employee SMITH, if called in, must work at least 6 hours, no more than 8 hours, earns $30/hour, and is only available between 6am and 8pm. filename = 'scheduling.xlsx' ĮmployeeName MinHours MaxHours HourlyWage Availability The first sheet in the Excel file (available here) contains the staff information, and it is in a tabular format suitable to be imported directly as a MATLAB table using the readtable function.
#Milp solver for excel code
This is a fictional dataset and the Excel file is available along with the MATLAB code for this blog post so you can feel free to try it out on your own. The staff information and scheduling requirements are in an Excel file, so we will first need to import the data. Read in the requirements and employee data from the Excel sheet This is probably a bit abstract, but let's load in some concrete data so you can get a clearer idea of what exactly we are trying to do here. If an employee is called for duty, they must work at least a specified minimum number of hours, and no more than a specified maximum number of hours.The customer's problem statement was as follows. Obviously the "Nurse Scheduling Problem" is not limited to "nurses" as an occupation, so I will just use the generic term "employee" here. This is a variation of what is known as the Nurse Scheduling Problem. The work in this blog post is based loosely on a discussion I recently had with a customer, who wanted to make an optimal shift schedule for his employees while satisfying certain availability and staffing constraints. There are a number of classic optimization problems that can be framed using MILP, such as the Travelling Salesman Problem and Knapsack Problem. This had been one of our most requested features, as MILP has trememdous application in a variety of areas such as scheduling and resource optimization. Since it's introduction in release R2014a, we've had several blog posts now showing some applications of intlinprog, the mixed-integer linear programming (MILP) solver found in the Optimization Toolbox. Mixed-Integer Linear Programming and The Nurse Scheduling Problem Call intlinprog with every variable as an integer 0 or 1 Mixed-Integer Linear Programming and The Nurse Scheduling Problem.
0 kommentar(er)
