MrExcel Podcast is Sponsored by Easy-XL Learn Excel from MrExcel Podcast, Episode

1830: Use Solver. Hey, welcome back to the MrExcel netcast,

I’m Bill Jelen. Today’s question’s sent in by Haizen. Haizen is trying to arrange Transport for

a number of employees– anywhere from 1 employee To 500 employees. And here’s what’s available to me– you can

rent a Car for $200 with a capacity of 8, you can rent a Microbus for $230 with a capacity

of 14, you can rent a Minibus for $400 with a capacity of 26, and he wants a formula to

figure out the right number of Cars, Microbuses, and Minibuses. Alright, now, let’s just pause for a second

while I set this up. First thing I want to do, is I’m going to

build a table here from 1 To 500 so that I can see every possible case. I select this 1, go to the Fill, Series, we’re

going to Fill in columns from 1 To 500, click OK, and there’s our numbers. Alright, now. As Haizen said, this is easy for the first

8 People. We get 1 Car, and then for People 9 through

14, you get 1 Microbus, and then for the number People, 15 through 26, 1 Minibus. But the 27 is interesting– 27, you would

think you might get 1 Minibus and 1 Car, but that is more expensive than 2 Microbuses. Alright, so, it’s complicated. Now, let’s build a quick formula here to figure

out the total Capacity that we’ve arranged, and the total Cost. So, H1 Car times H1 Car over here– I’ll press

F4– plus Microbus, times Microbus over here– Press F4– plus Minibus, times Microbus over

here– Press F4, that figures our Capacity. During the pause, I created a similar formula

there for cost, and filled in all the blanks with zeros. We will double-click to shoot this down. Alright, and my initial reaction is, I need

to figure out how this data is working, right? And, so, I was just going to do it– just–

horrible, brute force method, and so for 27 People, if we tried 1 Minibus in 1 Car, we’ve

spent $600, but if we tried 2 Microbuses and no Minibuses, $460– clearly, that’s cheaper

and, hey, by the way, that holds 28 people. So, that means all of the answers down to

28 are going to be exactly the same, so I built a little set of formulas here, that

will copy the answer above straight down because I don’t have to solve all 500 of these problems;

I only have to solve when this number here is not enough to cover the number of People

I have. In fact, I can set up Conditional Formatting–

Alt+OD, New Rule, Use a formula, and that formula’s going to be based on Cell E29 anytime

that=E29

today, it quickly solved this, but it solved by saying that we needed some fractional number

of Minibuses and that’s not the way it works– you can’t have a fractional number of Minibuses. So, we’re going to add some New Constraints

that says that this Cell Reference, the number of Cars, must be an integer– it can’t be

a fractional number of Cars– and then the number of Microbuses must be an integer, and

then, finally, the number of Minibuses must be an integer. Now, at this point, this is the last Constraint,

so I click OK instead of add– so, there are my Constraints. GRG Nonlinear is probably the slowest Solver,

but it works more often than not. Alright, so, I’ve always used that method,

and then, we click Solve. You can watch down here in the lower left-hand

corner as it’s running what it’s trying to do. But it comes up with 1 Car and 1 Minibus as

the answer, with $600– Click OK. And, what’s interesting, is that must be the

right answer for all of the answers from 29 up through 34, ok? So, I’m still going to do a little bit of

manual effort here– I’m going to go back to Sheet 1, find 29, which is the first place

where we have something in red over here, and say that we need 101. And we have a cost of $600 and, sure enough,

that’s good all the way down until we get to row 35. So back here, 35, Solver, all of the parameters

are still there, I just have to click Solve, click OK, and now it’s 0, 1, and 1, and that

will get us good up until row 40. I’m going to pause. Alright, at this point, you know, I’m ready

to send this back, but Haizen asked for a formula and so I’m afraid that he has thousands

of rows and this really isn’t going to be a good way. If he has thousands of rows, then we just

want to do a VLOOKUP into this table. But we have to finish populating this table

which, at this rate, is going to be mind-numbingly boring, alright, because, you know, you would

copy those values over and then do 41. So, I want to see what would happen if I recorded

a macro that would handle the Solver part. So, I’m going to do– first off, this is xlsx,

which is the one horrible Excel format that does not allow macros, how silly is that?–

so, we have to do File, Save as, and we’re going to save it, not with an xlsx format,

but with xlsm or xlsb– either one of those is macro-enabled, so we’ll click Save. Alright, so, now, we’re allowed to have macros. Also, if you’ve never used macros before,

Alt+TMS and make sure that you’re not set up there on that top one. The second one is fine– Disable all macros

with notification– but, don’t use the top one because then you won’t be allowed to use

macros. Alright, so out here on the View tab, Macros,

Record Macro, and– just– I’ll call it RunSolverOnce– let’s see, Ctrl– I’m going to put Shift+S

in there– Control+Shift+S for Solve– Store macro in This Workbook, click OK. And now I’ve already keyed 41 in here, I come

out to the Data tab, Solver, click Solve, click OK, click Stop Recording, and let’s

go look at that Code and see what happens. So, do Alt+F11 to get to VBA, double-click

on Modules, double-click on Module1, and sure enough, they are doing things– it is recording

Code, isn’t this awesome? And all of this stuff here– this SolverOk–

since that’s not changing, we just have to do SolverSolve over and over and over again. This is going to be really good. Alright, another pause there while I went

through and recorded the answer for 41. Next thing we have to solve is 43, so Alt+F11,

RunSolverOnce, we click Run, and we get Compile error, Sub or Function not defined. Alright, now I have to thank John Peltier,

he has a great Website that documents what the problem is here. We have to go to Tools, References, and Add

a Reference to Solver– and this References includes all open VBA projects, so–we Add

that Reference to Solver, and now, when we run this macro– so we’re trying to run for

43– that one simple change allows it to work. You see, it’s trying to solve the problem

down here but we’re getting the OK message. Alright, I’m going to pause again while I

record this answer for 43. Okay, great, I’m back. Ctrl+Shift+S to run the next one and, boy,

it’d be nice if we get rid of this OK, so, we didn’t have to click OK– we could actually

have it in some sort of a loop. Hang on a second– see, now in the background

here, after each time that I run Solver I come back and I key in the right answers and

then look for the next spot where they– we– don’t have enough, and we’ll

run Solver for 55. And, so, I kind of have this feeling of what

I’m doing manually, and it’d be nice if we could write VBA that would do all of those

steps. For example, it would record the 0, 2 and

1, in the right spot, and then increment this by 1 and add it into Cell C1. Okay, here’s a quick bit of Code that I knocked

out. We’re working with two different worksheets–

Sheet1 and Sheet2– so I declare those as Worksheets. Dim WS1 as worksheet, Dim WS2 as Worksheet,

Set WS1=Worksheet1, then WS2=Worksheet2. This is our recorded Code, SolverSolve, and

then I’m going to figure out which row we’re running to. So, if we’re working on Employee number 55,

that’s going to be written to employee– row– number 56 because I have one row of headings

back here on Sheet1. And then WS1, on Sheet 1, go to WhichRow,

Column2, that’s Column B, 1 row, 3 columns wide. is going to be Equal to the value from

Row 4, Column 1, Row 5, Column 1, Row 6, Column 1. So, right there, that is a single line of

code with space, underscores as our continuation characters at the end. And then, figure out which to solve for next. So, when we solve for 55, this number, here,

might be 60, and so we know that everything from 55 to 60 is the same Value and we will

want this to be– this 60 to be– copied up here as 61. So, WS2.Cells(1, 3).Value=WS2.Cells(7, 5).Value

+ 1. Alright. And then, we still have the problem that we

have to answer OK every time that we run Solver.Solve, but at John Peltier’s site, I learned that

if I made this into a function, x=SolverSolve, and in parentheses, this user finish here,

say (True), means that it will not show us the OK dialog box or the dialog box where

we have to hit OK. Alright, so this is kind of exciting now,

because it’s about to go solve this one, log the answers for me, and then be ready to run

the next one. So, I’m just anywhere here, I’ll click OK,

I see that it’s down here trying to do its thing and came up with an answer of 0 for

0, which will handle us for employees up to 56, and then it already put the right answer

in here of 57. Let’s go check and make sure that it put the

right answers back here– 0 for 0– so that’s the answer for Employees 55 and 56. It’s really kind of funny how sometimes we

get a big, huge string, and sometimes it’s just a couple before the answer changes. But, sure enough, row 50– or– 57 employees

is where it changes again. Alright, I can keep hitting Ctrl+Shift+FS,

Ctrl+Shift+FS, Ctrl+Shift+FS, to have it solve one at a time, but I’m still going to have

to be here through lunches, I work on getting this all the way up to 500. So we’ll come back to our Code and right down

here at the end, we’re going to say, If WS2.Cells(7, 5).Value>500 Then Exit Sub — just go ahead

and finish– otherwise (Else), GoTo RunAgain End If. And RunAgain is just a label that we’re going

to have up here. Put in a colon after it, and it says that

this will just go ahead and run again. So, it’s going to solve one, log the answers,

increment the Value, and then run again, and it’s going to keep doing it until we get to

500. Alright? So, right now, November 27th, it’s 11:02 a.m.,

I’m going to let this start to run– Ctrl+Shift+S. I see down here that it is working on something

and then it ups this number from 61 to 67 to 69, and so on. So, it’s going to be running for a while,

again I’ll pause the video. Okay, there we are– 1107. Took about five or six minutes and we’ve gotten

all the way up where it has solved for every possible combination of– from– 1 Passenger

up through 500 Passengers. We know what the answer is. Now, I’m just curious if there’s any sort

of a pattern here, so I’m going to let Excel create a chart for me of just the Car, Microbus,

and Minibus. Make sure we’re at the top there, see what

the Recommended Chart is. I think it has to be a stacked Bar charts–

that’s interesting came up with a Line chart, I’m not going to go with any of those– I

want to see a Stacked Column Chart– Stacked Column Chart, actually. So, right here, Stacked Column, click OK. Yeah, I don’t– okay. So, I don’t see any clear trends there. Maybe there’s a better way to do this to figure

out what the trend is. And is there math that could minimize this? That’s a great question. I didn’t pay enough attention in Math class

to know the answer to that. Some of you watching this on YouTube may say. “Hey, there’s better math,” but the dangerous

or good thing– good thing or dangerous thing– is that, using Solver and a little bit of

macro code, I was able to just brute-force run through all 500 possibilities and come

up with the correct answers. Now, what do I do if I’m Haizen? What if I– what if, what is my future way

to use this? If I have thousands of different possibilities,

I have to solve them all at once, I’m going to do a VLOOKUP into this table. Alright? But, if it’s a tool, especially where if the

cost changes daily, and for each one I have to solve it, well, then I’m probably just

going to want a macro that will be RunSolverOnce. Okay, so, here, let’s make this RunSolverAll

and then I’m going to copy that Code. I’m Just going to select from here all the

way down to the bottom, Ctrl+C, and RunSolverOnce. And let’s see. I don’t even know if we have to record the

answers or not, I’m not going to have it increment, not going to have any of this down there. So, this little macro now will run Solver

one time, and would just be a great tool to use on a go-forward basis to solve one at

a time. So, I’m going to insert a little shape here–

rounded shape– and we will add a Text box– “Solve for Cell C1″– let’s do a little Align

Center, Align Right, actually hit one extra Return there, increase the size, and then,

finally, right-click, Assign Macro, and that’s going to be called RunSolverOnce. And, so, you know, we have a new cost. So, Today, the cost of a car is 195, the cost

of a Microbus is 235, Minibus is still 400, and we have to get 42 Passengers, Solve for

Cell C1, that will go out and RunSolverOnce, 030. So, now we have this great little tool where

we’re good to go– that’s if we’re allowed to do one at a time. If we really need a formula, well, then, you’re

going to have to update these costs and have a second button here. I’m going to Ctrl-click this, Ctrl+drag, and

this will be Solve Entire Table, We’ll change the macro here, and the colors, so back on

Format, choose a different color, and right-click, Assign Macro, RunSolverAll, click ok. Actually, I’m going to go back to that Code

and make sure that we start out with that WS2.Cells(1, 3).Value=1 and, also,

WS1.Cells(3, 2).Resize(499,3).FormulaR1C1=”=R(-1)C”. That resets everything to be the value just

above. Alright, so, this is actually RunSolverAll. Alright, so we have two different buttons

there– one to just solve a single time, one to solve the entire table and then our VLOOKUPs

would work. Alright, so we covered an awful lot in this

video. I started out with installing Solver, setting

up Solver to run once, using VBA to run the existing Solver over and over and over again,

how to use VBA to log the results, using VBA to run all possible– the entire universe

of possibilities– so that way, we have a table of all of the answers already calculated. Again, I want to refer you out to John Peltier’s

site– peltiertech.com/Excel/SolverVBA.html– he has great VBA examples there if you need

to use Solver. Like, I was able to run it first, which loads

Solver; if you want VBA to run it the first time, you have to look at the code out there. So, this is, unfortunately, the best answer

that I have– is pre-building this table and then using VLOOKUPS based on the number of

Passengers, to get the correct answers. Probably not– probably much more convoluted

than what Haizen was looking for. If you know a better way to do this– you

know, think, is there matrix multiplication, is there a way to solve simultaneous equations,

is there some other way to solve this without the brute-force method of Solver? Please, let me know down in the YouTube comments

right down. Alright, hey, I want to thank you for stopping

by, we’ll see you next time for another netcast from MrExcel.

I'm guessing there would be a way to start a formula by looking at filling the largest vehicle as many times as possible until the remainder reaches a certain threshold. Then scale down. That makes some assumptions about the relative costs per passenger of the vehicles ofc. Might have to play with this one…

First of all let me seize the opportunity to thank you very much for your help and support , and wasting your time for helping people that is prove to me that humanity still fine and great , A gain thank you very much

Great video!

This could have been/ be a great Dueling Excel Gurus episode. I wonder, if Mike Girvin could whip together an array formula to solve this. True is, that there is the monetary and integer constraint, which an array formula may not be able to address.

Good video lots to think about.

I've found many times the VBA method coupled with a few formulas in a loop can solve complex tables like this.

Still always think there must be a formula way to achieve this, most likely an array formula.

@ExcelisFun could perhaps give it a go.

Seems like a formula to solve for the optimal solution would be unreasonably complex. For this problem, you could use the simplex method for faster calculations.

Bill, this is an excellent video. I was looking for a method to maximize a goal (ending Profit of $xx mil) by recognizing prior year Revenue ($xx mil) constrain and current year max of $xx. It sure helped me a lot. Thanks!!

Hi Bill, great video. Do you have a solution for "the solver" problem for mac users?

Just what i needed, good job!

Thanks for the video!, when I specify solver parameters there is a way to put in" the value off" a cell and not a number? I have a cell that is gonna be changing and dont wanna have to use solver every single time to refresh the values

I also feel like there should be an algebraic method of solving this problem. The issue is that classical methods of algebraic solution for this kind of problem will yield a fractional result, which is obviously not satisfactory. If it were me, I'd look for a correlation between the fractional solutions and the integer solutions. If there is a relationship as easy as rounding, it would not be too difficult to derive an equation for the fractional solution and then convert it into an integer solution.

I'd love to do it myself, but it's almost 3am and if I open excel I probably won't sleep until tomorrow night.

Bill Jelen for President!!

Just great, thanks Bill

THANKS.. that was really helpfull…

Amazing video.

Thank you so much.

Thank you.

My tools tab doesn't have "references" listed below it.. 🙁

best explanation ….hats off your effort..

Thankyou