Help me use Excel to save my job
June 19, 2014 4:59 PM Subscribe
So, I just got promoted to a new position at my company (Yay!). Unfortunately, the new requirements involve scheduling a ridiculous number of people. It turns out my predecessor has just been doing dozens of iterations manually until one kind of worked, but every slight change puts a bunch of other things out of wack.
I feel like Excel could probably be capable of doing it, but I have no idea how I would go about designing such a spread sheet.
Here is an analogy I've constructed, with the analogy of having to fill a maintenance schedule.
I imagine it would require VBA, would it be difficult to do in VBA?
If so, where would be a reputable place where I could pay someone to build this for me and what would be a reasonable cost?
Here is an analogy I've constructed, with the analogy of having to fill a maintenance schedule.
- I have a table with a list of names, their rank, their days off.
- Every night has to have a plumber and a sweeper on.
- Workers can’t work 2 shifts in a row.
- There are 3 ranks. Master plumber, Plumber, and Junior plumber. Junior plumbers can only sweep when a master plumber is on with them.
- The workers are very sticky about equal distribution. They’ve agreed to a “point system” where sweeping is 3x harder than plumbing, and that weekend shifts are 2x harder than weekday shifts.
- They really want every worker to have a similar (if not equal) number of points.
- They each have a list of days off.
- Ideally, the shifts are as far away from each other as possible.
I imagine it would require VBA, would it be difficult to do in VBA?
If so, where would be a reputable place where I could pay someone to build this for me and what would be a reasonable cost?
This actually sounds pretty complicated to implement.
The way I would do it is to devise some kind of script for randomly generating a 'valid' schedule, where valid includes all of the must-have criteria, i.e. points 2, 3, 4, and 7 are met. Then I would come up with a numeric score which indicates how well the schedule meets the optional criteria, i.e. points 5, 6, and 8. Then I would generate a huge number of random schedules and pick the one with the best score.
To generate random schedules I would do something like this:
- generate a list of valid plumbers for this shift (haven't worked previous day, not a day off)
- generate a list of valid sweepers
- randomly select a plumber and a sweeper
(possibly instead of random, you could preferentially select one whose previous shift was furthest away)
Fill the shifts this way one at a time. You'll have to add some logic for junior/master plumbers too, and for having more than one plumber or sweeper if applicable.
To score schedules I would count up the point system for each person and come up with some kind of statistic expressing how well the scores fit your criteria. Like the standard deviation, or the maximum distance from the mean.
The next challenge you'll hit is that just generating a ton of random schedules might not actually land you on a good one. This would take some experimentation. There are more advanced ways of solving this kind of problem (see algorithms like simulated annealing) but this is not going to be trivial to implement.
You could hire a freelance programmer to do this on odesk.com. It could be done in VBA by someone who knows what they're doing. For the first part -- randomly generating correct schedules, calculating a score, and selecting a 'good' schedule from a large random batch -- maybe a 20 hour contract, initially? Expect there to be significant follow-up and tweaking to make sure things are right.
posted by PercussivePaul at 5:28 PM on June 19, 2014 [1 favorite]
The way I would do it is to devise some kind of script for randomly generating a 'valid' schedule, where valid includes all of the must-have criteria, i.e. points 2, 3, 4, and 7 are met. Then I would come up with a numeric score which indicates how well the schedule meets the optional criteria, i.e. points 5, 6, and 8. Then I would generate a huge number of random schedules and pick the one with the best score.
To generate random schedules I would do something like this:
- generate a list of valid plumbers for this shift (haven't worked previous day, not a day off)
- generate a list of valid sweepers
- randomly select a plumber and a sweeper
(possibly instead of random, you could preferentially select one whose previous shift was furthest away)
Fill the shifts this way one at a time. You'll have to add some logic for junior/master plumbers too, and for having more than one plumber or sweeper if applicable.
To score schedules I would count up the point system for each person and come up with some kind of statistic expressing how well the scores fit your criteria. Like the standard deviation, or the maximum distance from the mean.
The next challenge you'll hit is that just generating a ton of random schedules might not actually land you on a good one. This would take some experimentation. There are more advanced ways of solving this kind of problem (see algorithms like simulated annealing) but this is not going to be trivial to implement.
You could hire a freelance programmer to do this on odesk.com. It could be done in VBA by someone who knows what they're doing. For the first part -- randomly generating correct schedules, calculating a score, and selecting a 'good' schedule from a large random batch -- maybe a 20 hour contract, initially? Expect there to be significant follow-up and tweaking to make sure things are right.
posted by PercussivePaul at 5:28 PM on June 19, 2014 [1 favorite]
In math-speak, what you've described is basically an "integer program" with constraints, and you're looking to optimize a weighted objective function. It is almost definitely solvable -- the question is how good does the solution have to be, and what's your budget?
I believe that if you want to implement it yourself, you can indeed do this in excel using the solver add-in, and the right combination of tables. If there is no 'excel guru' in your office who would be willing to take this on, I suspect it would be a cheap thing to ask someone on the internet to do for you. You can check out o-desk for similar jobs.
On Preview: I agree with PercussivePaul that the hardest part will be making sure that the odesk person did a good job.
posted by tinymegalo at 5:32 PM on June 19, 2014 [2 favorites]
I believe that if you want to implement it yourself, you can indeed do this in excel using the solver add-in, and the right combination of tables. If there is no 'excel guru' in your office who would be willing to take this on, I suspect it would be a cheap thing to ask someone on the internet to do for you. You can check out o-desk for similar jobs.
On Preview: I agree with PercussivePaul that the hardest part will be making sure that the odesk person did a good job.
posted by tinymegalo at 5:32 PM on June 19, 2014 [2 favorites]
What you want is the Solver plug-in, with a measure of the even-ness of points as your objective. The tricky part will be setting up your constraints.
A long time ago, I built a workforce planning model for a cruise line holding company using Excel, so I'm pretty darn sure this will be possible, but it may be tricky.
I agree that Python would be a more elegant approach. oDesk is the way to go if you want to hire someone for that. Just make sure your problem is very well specified. And consider hiring 2+ programmers independently, in case one or more flakes out.
posted by CruiseSavvy at 5:33 PM on June 19, 2014 [3 favorites]
A long time ago, I built a workforce planning model for a cruise line holding company using Excel, so I'm pretty darn sure this will be possible, but it may be tricky.
I agree that Python would be a more elegant approach. oDesk is the way to go if you want to hire someone for that. Just make sure your problem is very well specified. And consider hiring 2+ programmers independently, in case one or more flakes out.
posted by CruiseSavvy at 5:33 PM on June 19, 2014 [3 favorites]
Best answer: This is why they invented PSAs (professional services automation software). The amount of time you're going to spend creating and maintaining this system (that you will inevitably be stuck with because absolutely no one else will ever learn how to use/maintain it).
Creating this thing is going to make you the scheduling bitch for all eternity. Implement a real PSA, and you can knock out the scheduling piece (including the point system), and you can take advantage of all the other features a PSA offers. You'll be a rock star. It looks great on your resume, too.
I do this for a living (BI/PSA consulting/development for professional services organizations), and I can't even begin to count how many of these insanely complex spreadsheets (and painfully manual processes) finally get abandoned while some poor frazzled soul quietly weeps in the corner. The usual progression involves a lot of painful months/years of tweaking the spreadsheet, beating up the spreadsheet "owner" (see the crying person above), requesting "minor" modifications that break the whole damned thing, and upper management finally getting fed up and throwing down a whole wad of cash for a real system. The amount of time wasted on these endeavors makes my skin crawl (I guess that's why I'm a good fit in my role).
You could also look into tools that are strictly for scheduling/dispatch (vs. a full-featured PSA with ticketing, invoicing, workflow management, etc.) A lot of them are month-to-month cloud-based subscription services that integrate with Outlook and mobile devices. They'll be cheaper than a full-on PSA, if cost is a big factor. You'll still be able to build in your business rules (like requiring a master plumber for a junior plumber to sweep) and get reporting that will allow you to accommodate the point system (if it doesn't natively handle a point system - some of the PSAs and CRMs do...can't speak for the scheduling tools).
It's been a long day, so if I'm rambling or not making sense, forgive me. :)
TL;DR: I assume you have other job responsibilities, and this sounds like it easily take up a ton of your time when you should be mastering your new job's core responsibilities. Unless this is a skill you'll make good use of in the future, I wouldn't invest my time in reinventing the wheel. They make software applications that do these things.
posted by nobejen at 5:43 PM on June 19, 2014 [16 favorites]
Creating this thing is going to make you the scheduling bitch for all eternity. Implement a real PSA, and you can knock out the scheduling piece (including the point system), and you can take advantage of all the other features a PSA offers. You'll be a rock star. It looks great on your resume, too.
I do this for a living (BI/PSA consulting/development for professional services organizations), and I can't even begin to count how many of these insanely complex spreadsheets (and painfully manual processes) finally get abandoned while some poor frazzled soul quietly weeps in the corner. The usual progression involves a lot of painful months/years of tweaking the spreadsheet, beating up the spreadsheet "owner" (see the crying person above), requesting "minor" modifications that break the whole damned thing, and upper management finally getting fed up and throwing down a whole wad of cash for a real system. The amount of time wasted on these endeavors makes my skin crawl (I guess that's why I'm a good fit in my role).
You could also look into tools that are strictly for scheduling/dispatch (vs. a full-featured PSA with ticketing, invoicing, workflow management, etc.) A lot of them are month-to-month cloud-based subscription services that integrate with Outlook and mobile devices. They'll be cheaper than a full-on PSA, if cost is a big factor. You'll still be able to build in your business rules (like requiring a master plumber for a junior plumber to sweep) and get reporting that will allow you to accommodate the point system (if it doesn't natively handle a point system - some of the PSAs and CRMs do...can't speak for the scheduling tools).
It's been a long day, so if I'm rambling or not making sense, forgive me. :)
TL;DR: I assume you have other job responsibilities, and this sounds like it easily take up a ton of your time when you should be mastering your new job's core responsibilities. Unless this is a skill you'll make good use of in the future, I wouldn't invest my time in reinventing the wheel. They make software applications that do these things.
posted by nobejen at 5:43 PM on June 19, 2014 [16 favorites]
I like nobejen's answer, especially the part were you don't write it yourself.
I've written something like this, scheduling high school students to classes they selected, keeping in mind their closeness to graduation, the times the classes were offered, and the total capacity of each class.
It sounds fun, really. I doubt you'll be able to use a spreadsheet, except perhaps to carry state between runs of the scheduling program. The process is likely to be iterative, where you repeatedly run through the list of employees with the lowest number of accumulated points, and try to assign them to shifts.
This assignment to shifts would be constrained by days off, rank, and shift adjacency. The solutions do not have to be optimal. I suspect the solution space is dense, as you have many employees, and I think it unlikely that you would have situations were you could not schedule anyone for a shift.
I wouldn't do it in VBA: since this is a piece of code you're likely to pass on to a successor, I'd use something less tied to any one vendor. Any modern language would suffice.
Spend some time working up the request for quote, because that will be a big part in getting competent responses, and get several bids. Details like carrying the current state in a spreadsheet, and having the results in thus and such a form of spreadsheet are very useful later for evaluating completion.
I guess I'm first to cough up a dollar amount:50-100 hours, say, $5000-$10,000.
posted by the Real Dan at 5:57 PM on June 19, 2014
I've written something like this, scheduling high school students to classes they selected, keeping in mind their closeness to graduation, the times the classes were offered, and the total capacity of each class.
It sounds fun, really. I doubt you'll be able to use a spreadsheet, except perhaps to carry state between runs of the scheduling program. The process is likely to be iterative, where you repeatedly run through the list of employees with the lowest number of accumulated points, and try to assign them to shifts.
This assignment to shifts would be constrained by days off, rank, and shift adjacency. The solutions do not have to be optimal. I suspect the solution space is dense, as you have many employees, and I think it unlikely that you would have situations were you could not schedule anyone for a shift.
I wouldn't do it in VBA: since this is a piece of code you're likely to pass on to a successor, I'd use something less tied to any one vendor. Any modern language would suffice.
Spend some time working up the request for quote, because that will be a big part in getting competent responses, and get several bids. Details like carrying the current state in a spreadsheet, and having the results in thus and such a form of spreadsheet are very useful later for evaluating completion.
I guess I'm first to cough up a dollar amount:50-100 hours, say, $5000-$10,000.
posted by the Real Dan at 5:57 PM on June 19, 2014
To start, I'd set up a spreadsheet to calculate the points when you enter names in a weekly calendar. You'd still need to try different iterations, but it would calculate the points easily, and wouldn't be too hard to do the required items making sure the roles are fulfilled for each shift. Scheduling is pretty difficult, I'd look for a scheduling application.
posted by theora55 at 6:28 PM on June 19, 2014
posted by theora55 at 6:28 PM on June 19, 2014
They make special software for this. Right tool for the job and all of that. Some are free, or very cheap. Check out Microsoft Scheduler or when to work. Google scheduling programs and bask in the goodness of not re-inventing the wheel.
posted by Ruthless Bunny at 6:48 PM on June 19, 2014 [3 favorites]
posted by Ruthless Bunny at 6:48 PM on June 19, 2014 [3 favorites]
I agree that this is an integer programming problem, and can be solved with Excel using Solver. What solver does for you is find the optimal solution given a set of constraints, saving you from having to manually cycle through a lot of options.
The hard part is setting up the problem so that Excel will understand what you want. I studied how to do this in an advanced analytics class in my master's program (we even did a similar kind of scheduling problem). So it is definitely possible.
HOWEVER, no one came back after we completed the problem and said we had to add a widget-polisher job with its own set of specifications to the problem, or purchased a Sweep-a-Tron 4000 that made sweeping easier than plumbing thereby changing the points system.
If I had to deal with this in real life, in my real job, I'd get someone with scripting skills to build it out, or try to convince my employer to buy software that could handle this.
posted by jeoc at 6:51 PM on June 19, 2014
The hard part is setting up the problem so that Excel will understand what you want. I studied how to do this in an advanced analytics class in my master's program (we even did a similar kind of scheduling problem). So it is definitely possible.
HOWEVER, no one came back after we completed the problem and said we had to add a widget-polisher job with its own set of specifications to the problem, or purchased a Sweep-a-Tron 4000 that made sweeping easier than plumbing thereby changing the points system.
If I had to deal with this in real life, in my real job, I'd get someone with scripting skills to build it out, or try to convince my employer to buy software that could handle this.
posted by jeoc at 6:51 PM on June 19, 2014
Yeah, someone else has invented this for you for less than it would cost you to have someone develop an in-house solution. I personally use a scheduler called Amion which was developed for hospitals but you could actually use it for anything shift-based, I'm pretty sure. I started using it a couple of months after being promoted and starting to do the schedule and realizing that the Excel spreadsheet we had been using was a) miserable for all the reasons you described and b) if there were last-minute changes it had to be distributed all over again. Amion has the benefit of being a web-based calendar so changes are updated constantly. I'm sure any of the other software suggested here would also do the same thing.
posted by The Elusive Architeuthis at 7:16 PM on June 19, 2014 [1 favorite]
posted by The Elusive Architeuthis at 7:16 PM on June 19, 2014 [1 favorite]
I used to use a free program called ABC Roster for this kind of thing. You should be able to put in a series of requirements re: availability and have it spit out a schedule which you can then tweak.
posted by spunweb at 8:32 PM on June 19, 2014
posted by spunweb at 8:32 PM on June 19, 2014
Brute force method - generate a buttload (10^butt, technically) of solutions to your program and search through for one that meets your constraints. Excel's solver plugin is dog slow for real world sized IPs.
And then don't allow anyone to deviate from it.
Or definitely use an easy-to-use scheduler such as those given above...
posted by cromagnon at 7:18 AM on June 20, 2014
And then don't allow anyone to deviate from it.
Or definitely use an easy-to-use scheduler such as those given above...
posted by cromagnon at 7:18 AM on June 20, 2014
I've solved similar problems with the language Prolog. Once you figure it out it feels like it was designed for this sort of problem. You write rules, then you ask for answers.
posted by bdc34 at 8:28 AM on June 20, 2014
posted by bdc34 at 8:28 AM on June 20, 2014
I agree with bdc34 that this sounds like a textbook Prolog problem.
Pro: You'd be using the One Perfect Tool for this particular job
Cons: You'll have to invest some hours learning to use it. And you might not ever meet anyone else who uses Prolog.
posted by paper chromatographologist at 10:16 AM on June 20, 2014
Pro: You'd be using the One Perfect Tool for this particular job
Cons: You'll have to invest some hours learning to use it. And you might not ever meet anyone else who uses Prolog.
posted by paper chromatographologist at 10:16 AM on June 20, 2014
Response by poster: Thank you all for your wonderful answers. I've worked through many of them, with limited success.
I tried Amion and ABC roster, and they were very very promising, but ultimately fell short.
I don't have 10 000$ to spend on this, so hiring an in-house solution was out of the question.
Excel SOLVER seemed awesome, but then it failed to even solve a simple 3 person schedule so that people weren't on 2 days in a row. Here is a template I used: https://www.dropbox.com/s/3owex2z774wkz7v/cacofonie.xlsx
And Prolog sounds awesome and fun, but I have no programming experience, and even though I spent an hour on this site (http://www.learnprolognow.org/), I couldn't see myself being able to master it without >50 hours of work.
So, back to square one. A kind meFite has offered to try and solve it for me, and otherwise I may just use excel to automate counting while I do the grunt work.
Thanks again, you guys are all amazing.
posted by cacofonie at 2:12 PM on June 21, 2014
I tried Amion and ABC roster, and they were very very promising, but ultimately fell short.
I don't have 10 000$ to spend on this, so hiring an in-house solution was out of the question.
Excel SOLVER seemed awesome, but then it failed to even solve a simple 3 person schedule so that people weren't on 2 days in a row. Here is a template I used: https://www.dropbox.com/s/3owex2z774wkz7v/cacofonie.xlsx
And Prolog sounds awesome and fun, but I have no programming experience, and even though I spent an hour on this site (http://www.learnprolognow.org/), I couldn't see myself being able to master it without >50 hours of work.
So, back to square one. A kind meFite has offered to try and solve it for me, and otherwise I may just use excel to automate counting while I do the grunt work.
Thanks again, you guys are all amazing.
posted by cacofonie at 2:12 PM on June 21, 2014
Not sure how many people you're scheduling for, but I tried this several months ago for a non-profit. Sadly, they had no discount for non-profits and I had to abandon it.
http://wheniwork.com/employee-scheduling-software/
Good luck!
posted by bach at 6:04 PM on June 22, 2014
http://wheniwork.com/employee-scheduling-software/
Good luck!
posted by bach at 6:04 PM on June 22, 2014
There's a great 12 minute film about the married couple who did 20 years of this work for Major League Baseball: the Schedule Makers.
posted by alms at 10:56 AM on July 1, 2014
posted by alms at 10:56 AM on July 1, 2014
This thread is closed to new comments.
posted by deathpanels at 5:10 PM on June 19, 2014 [2 favorites]