Solver is a free add-in for Windows versions of Excel that can find optimal solutions for problems that are more complex than something Goal Seek can solve.
Solver has been a free add-in since the days of Lotus 1-2-3
Solver is a product of Visicorp founder Dan Fylstra
Solver in your Excel is a smaller version of heavy-duty solvers
Learn more about pro solvers: http://mrx.cl/solver77
To install Solver, type Alt+T then i. Check Solver.
Solver will be found on the right side of the Data tab
You want to have an objective cell that you are trying to minimize or maximize.
You can specify multiple input cells.
You can specify constraints, inluding some that you would not expect:
No half-people: Use INT for Integer
Solver will find an optimal solution, but there might be others that are ties
Once you get the Solver solution, you might be able to tweak it.
Solver has been a free add-in since the days of Lotus 1-2-3
Solver is a product of Visicorp founder Dan Fylstra
Solver in your Excel is a smaller version of heavy-duty solvers
Learn more about pro solvers: http://mrx.cl/solver77
To install Solver, type Alt+T then i. Check Solver.
Solver will be found on the right side of the Data tab
You want to have an objective cell that you are trying to minimize or maximize.
You can specify multiple input cells.
You can specify constraints, inluding some that you would not expect:
No half-people: Use INT for Integer
Solver will find an optimal solution, but there might be others that are ties
Once you get the Solver solution, you might be able to tweak it.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2036 - Intro to Solver!
Alright, I'm podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist, where you can play all the videos!
Welcome back to MrExcel netcast, I’m Bill Jelen. We talked about some What-If analysis recently, like Goal Seek, you know, with one input cell that you're changing, but what if you have something more complex? There's a great tool called Solver, Solver’s been around for a long time, I guarantee if you have Excel and you're running on Windows, you have Solver, it's just probably not turned on. So to turn it on, you have to go to Alt T and then I, so T for Tom, I for ice cream, and check this box for Solver, click OK, and after a couple seconds, you'll have a Solver tab out here on the right-hand side. Alright, and we're going to set up a model here that solver might be able to solve, we have an amusement park, we're trying to go out how many employees to schedule. Everyone works five consecutive days, so there's really seven possible schedules where you're off, Sunday Monday, Monday Tuesday, Tuesday Wednesday.
We have to figure out how many employees to put on each of those schedules.
And so just simple little math here, doing some SUMPRODUCTs, number of employees times Sunday to figure out how many people were there on Sunday, Monday, Tuesday, Wednesday.
And what we've learned through operating this amusement park is we need a lot of people on Saturday and Sunday. 30 people on Saturday and Sunday, during the week Monday, Tuesday, kind of slow, 12 staff will be able to do it. Alright so, just by coming along here and just screwing around, you know, trying to figure out the right numbers, you can just keep plugging things in, but with seven different choices, it would take forever, alright so.
Now in Solver, what we have is, we have a series of input cells, and in the free version of Solver I think you can have, is it a hundred?
I don't know, there's some number, and if you have to go beyond that, there's a Premium Solver that you can get from Frontline Systems.
Alright, so we have some input cells, we have some constraint cells, and then you have to bring it all down to a final number. So in my case, I'm trying to minimize payroll per week, so that green number is what I want to try and optimize, alright, so here's what we're going to do!
Solver, here's the objective cell, it’s the green cell, and I want to set that to a minimum value, figure out the staffing that gets me the minimum value, by changing these blue cells. And then here are the constraints, alright, so the first constraint is that the schedule total has to be >= the red section, and we can do all that as a single constraint.
Watch how cool this is, all of these cells have to be >= these corresponding cells here, awesome, click Add, alright, but then there's other things that you wouldn't think of. For example, Solver at this point might decide that it's best to have 17 people on this schedule, 43 people on the schedule, and -7 people on this schedule. Alright, so we have to tell Solver that these input cells have to be an integer, click Add. And also, we can't have someone not show up, and they'll give us their salary back, right? So we're going to say these cells have to be >=0, click Add, we go back now, we have our three constraints there.
There's three different ways to solve, and this one it follows linear math, so we can just go Simplex LP. If this one doesn't work, then by all means try the other two, I've had cases where the Simplex says it can't find a solution, and one of the other two work. Frontline Systems has great tutorials on Solver, I'm just trying to get you through your first one here today, I don't proclaim to be a Solver expert. Once I had a Solver that wouldn't work, and I sent a note into Frontline Systems, and wow, I got this awesome 5-page letter back, right, from Dan Fylstra himself, the president of Solver! And it started out: “Dear Bill, great to hear from you!” And then went on for 4.9 pages, that was all pretty much completely above my head, alright.
But you know, I know enough about Solver to get through this, alright, so we're going to click here on Solve, it found a solution, “All Constraints and optimality conditions are satisfied.” I'm going to keep that, I can create some reports, don't need to do that right now. Oh, I can actually save a scenario, I made fun of scenarios yesterday, maybe Solver would be able to create a new scenario for me, so we'll click OK.
Alright, and sure enough it's saved us money, we wrote 2584 before, and now it got us down to 2040. So we need a lot of people off on Monday and Tuesday, alright, some people, 2 people off on Wednesday Thursday, and then Friday Saturday. Well, this is awesome, I never would have just randomly come up with this set of answers, alright, but does that mean it's the best answer? Well, it means that it's the minimum payroll, but I can probably come up with a different set of answers that would still have this minimum payroll. There's other ways to do that, that might be a slightly better schedule. Like for example, right now we have 28 people on Wednesday and Thursday, when we only need 15 and 18, that's a lot of people. Think about who works at amusement parks, these are college kids on home for break, this is going to be trouble if we have that many extra people. And on Monday Tuesday, we’re dead even, exactly where we want to be. So that means if anyone I'm going call off sick, now we're going to have to, you know, call someone in and pay them time and a half, because they've already worked five other days.
Alright, so just with some simple little math here, if I would take 8 away from Monday Tuesday, and make it 10, and take those 8 and add them to Wednesday Thursday, alright. Now I have a Solver solution with the exact same answer, 2040, they got the right number of people.
I just balance out the schedule, and now we have 8 extra, 8 extra, 3 extra, and 2 extra, and exactly what we need on the weekend which are, you know, the full staff scenario. To me, this is slightly better than what Solver came up with, does that mean that solver failed?
No, absolutely not, because I never would have gotten this close without Solver. Once Solver gave me the answer, yeah, I was able to tweak it a little bit and get there, alright.
Tip #37, “40 Greatest Excel Tips of All Time”, getting near the end of that first 40, great little introduction to Solver. The guide to all of the podcasts in this series is here, “MrExcel XL - 40 Greatest Excel Tips of All Time”, you can have the e-book for just $10, print book for $25, click the “i” on the top-right hand corner!
Alright, recap: Solver, if you're in Windows versions of Excel, Lotus 1-2-3, it's there, it's created by Visicorp founder Dan Fylstra.
It's a free version of the heavy-duty solvers, here's a link to go check out the heavy-duty solvers, that'll be down in the YouTube comments.
It's probable they're just not installed, Alt T I, check mark Solver, look on the right side of the Data tab to find Solver. Alright, you have to have an objective cell that you're trying to minimize or maximize or set to a value, one range of input cells. Specify constraints, including something wouldn’t expect, like I had to say “No half-people” and “No negative people”. Solver will find the optimal solution, but there might be others that are ties and you might be able to tweak it to get a better solution.
Alright, there you have it, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Alright, I'm podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist, where you can play all the videos!
Welcome back to MrExcel netcast, I’m Bill Jelen. We talked about some What-If analysis recently, like Goal Seek, you know, with one input cell that you're changing, but what if you have something more complex? There's a great tool called Solver, Solver’s been around for a long time, I guarantee if you have Excel and you're running on Windows, you have Solver, it's just probably not turned on. So to turn it on, you have to go to Alt T and then I, so T for Tom, I for ice cream, and check this box for Solver, click OK, and after a couple seconds, you'll have a Solver tab out here on the right-hand side. Alright, and we're going to set up a model here that solver might be able to solve, we have an amusement park, we're trying to go out how many employees to schedule. Everyone works five consecutive days, so there's really seven possible schedules where you're off, Sunday Monday, Monday Tuesday, Tuesday Wednesday.
We have to figure out how many employees to put on each of those schedules.
And so just simple little math here, doing some SUMPRODUCTs, number of employees times Sunday to figure out how many people were there on Sunday, Monday, Tuesday, Wednesday.
And what we've learned through operating this amusement park is we need a lot of people on Saturday and Sunday. 30 people on Saturday and Sunday, during the week Monday, Tuesday, kind of slow, 12 staff will be able to do it. Alright so, just by coming along here and just screwing around, you know, trying to figure out the right numbers, you can just keep plugging things in, but with seven different choices, it would take forever, alright so.
Now in Solver, what we have is, we have a series of input cells, and in the free version of Solver I think you can have, is it a hundred?
I don't know, there's some number, and if you have to go beyond that, there's a Premium Solver that you can get from Frontline Systems.
Alright, so we have some input cells, we have some constraint cells, and then you have to bring it all down to a final number. So in my case, I'm trying to minimize payroll per week, so that green number is what I want to try and optimize, alright, so here's what we're going to do!
Solver, here's the objective cell, it’s the green cell, and I want to set that to a minimum value, figure out the staffing that gets me the minimum value, by changing these blue cells. And then here are the constraints, alright, so the first constraint is that the schedule total has to be >= the red section, and we can do all that as a single constraint.
Watch how cool this is, all of these cells have to be >= these corresponding cells here, awesome, click Add, alright, but then there's other things that you wouldn't think of. For example, Solver at this point might decide that it's best to have 17 people on this schedule, 43 people on the schedule, and -7 people on this schedule. Alright, so we have to tell Solver that these input cells have to be an integer, click Add. And also, we can't have someone not show up, and they'll give us their salary back, right? So we're going to say these cells have to be >=0, click Add, we go back now, we have our three constraints there.
There's three different ways to solve, and this one it follows linear math, so we can just go Simplex LP. If this one doesn't work, then by all means try the other two, I've had cases where the Simplex says it can't find a solution, and one of the other two work. Frontline Systems has great tutorials on Solver, I'm just trying to get you through your first one here today, I don't proclaim to be a Solver expert. Once I had a Solver that wouldn't work, and I sent a note into Frontline Systems, and wow, I got this awesome 5-page letter back, right, from Dan Fylstra himself, the president of Solver! And it started out: “Dear Bill, great to hear from you!” And then went on for 4.9 pages, that was all pretty much completely above my head, alright.
But you know, I know enough about Solver to get through this, alright, so we're going to click here on Solve, it found a solution, “All Constraints and optimality conditions are satisfied.” I'm going to keep that, I can create some reports, don't need to do that right now. Oh, I can actually save a scenario, I made fun of scenarios yesterday, maybe Solver would be able to create a new scenario for me, so we'll click OK.
Alright, and sure enough it's saved us money, we wrote 2584 before, and now it got us down to 2040. So we need a lot of people off on Monday and Tuesday, alright, some people, 2 people off on Wednesday Thursday, and then Friday Saturday. Well, this is awesome, I never would have just randomly come up with this set of answers, alright, but does that mean it's the best answer? Well, it means that it's the minimum payroll, but I can probably come up with a different set of answers that would still have this minimum payroll. There's other ways to do that, that might be a slightly better schedule. Like for example, right now we have 28 people on Wednesday and Thursday, when we only need 15 and 18, that's a lot of people. Think about who works at amusement parks, these are college kids on home for break, this is going to be trouble if we have that many extra people. And on Monday Tuesday, we’re dead even, exactly where we want to be. So that means if anyone I'm going call off sick, now we're going to have to, you know, call someone in and pay them time and a half, because they've already worked five other days.
Alright, so just with some simple little math here, if I would take 8 away from Monday Tuesday, and make it 10, and take those 8 and add them to Wednesday Thursday, alright. Now I have a Solver solution with the exact same answer, 2040, they got the right number of people.
I just balance out the schedule, and now we have 8 extra, 8 extra, 3 extra, and 2 extra, and exactly what we need on the weekend which are, you know, the full staff scenario. To me, this is slightly better than what Solver came up with, does that mean that solver failed?
No, absolutely not, because I never would have gotten this close without Solver. Once Solver gave me the answer, yeah, I was able to tweak it a little bit and get there, alright.
Tip #37, “40 Greatest Excel Tips of All Time”, getting near the end of that first 40, great little introduction to Solver. The guide to all of the podcasts in this series is here, “MrExcel XL - 40 Greatest Excel Tips of All Time”, you can have the e-book for just $10, print book for $25, click the “i” on the top-right hand corner!
Alright, recap: Solver, if you're in Windows versions of Excel, Lotus 1-2-3, it's there, it's created by Visicorp founder Dan Fylstra.
It's a free version of the heavy-duty solvers, here's a link to go check out the heavy-duty solvers, that'll be down in the YouTube comments.
It's probable they're just not installed, Alt T I, check mark Solver, look on the right side of the Data tab to find Solver. Alright, you have to have an objective cell that you're trying to minimize or maximize or set to a value, one range of input cells. Specify constraints, including something wouldn’t expect, like I had to say “No half-people” and “No negative people”. Solver will find the optimal solution, but there might be others that are ties and you might be able to tweak it to get a better solution.
Alright, there you have it, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
