Excel - Use Solver with VBA - Episode 1830

[@nimamohebian8930]

I found the answer of my problem Id been searching for it a long time, thank u so much sir.

[@JoaoGuilherme-nx8yk]

Amazing!! Thank you!!

[@giusepearturo]

thansk from Colombia

[@anuradhasoni5721]

best explanation ....hats off your effort.. Thankyou

[@XMelomanoX]

Just what i needed, good job!

[@talkohavy]

Amazing video. Thank you so much.

[@michaelconnors7668]

Solver uses the Broyden-Fletcher-Goldfarb-Shanno algorithm which is a non-linear optimization. Quattro Pro also uses the Broyden-Fletcher-Goldfarb-Shanno algorithm for its Optimizer tool. Solver looks a "black box" tool, but when you know what it is based on you can kind of understand what the solver does.

[@asierbernardomugarraflores678]

bro !! this video is amazing .. I love you !!! you didn't miss anything

[@justcuz9181]

Bill Jelen for President!!

[@mancalledhero]

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.

[@joangustavobritomaldonado4623]

THANKS.. that was really helpfull...

[@Lifesigns37]

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.

[@duffry]

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...

[@kaiser6242]

Thank you.

[@MrXL]

+Kaiser you are welcome!

[@kadenzxc]

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.

[@RomuloGDO]

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

[@janezklun]

Just great, thanks Bill

[@oberghaus]

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.

[@hobirumahan85]

I have many target in every cell and need to calculate from a column with random number

[@tryller007]

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

[@amitbinwal6362]

Sir when a solver method applied to a specific sheet and if we are on another sheet of the same workbook then it not solved the problem and paste zero values to the range of present sheet where we are. Please solve my problem. Hope you understand what I want to convey

[@MrXL]

The Solver in Excel was written by Frontline. Reach out to them using the e-mail address shown at this page: www.solver.com/solver-technical-support-information-support-and-upgrades I am far from an expert on Solver. But one thought: when you initially set up the model on Sheet1, did you specify ranges in the format of A1, A2, or did you say Sheet1!A1? Try to add the worksheet name to the ranges in the Solver dialog. Of course, if you use spaces or other punctuation in your sheet names, you would need to use apostrophers: ='My-Sheet With Bad-Chars'!A2

[@theapexpredator157]

I need Solver to accept formulas in the Variable Cells Line so that it can select only certain cells within a range. Is this possible? If not, is there an alternate method to achieve my goal?

[@MrXL]

As far as I know, the input cells can't be formulas. But check out Solver.com - they wrote Solver in Excel and are pretty responsive to questions sent in.

[@theapexpredator157]

@@MrXL Thanks for getting back to me MrExcel. I sent an email to [e-mail] and am now waiting for a response. My thinking is that if/since Solver doesn't accept formulas in the Variables Cell Line, that there must be some kind of other way to make it work. If not in Excel, maybe there is another way with another Solver application or something... Not sure, but I'm hopeful. Thanks again!

[@michellerahmoeller191]

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

[@jk501111]

I had the same, had to click stop (the box) and then click Tools and References should activated now

[@charlesbyrneShowComments4all]

Application.Screenupdating = false before you start. Before it exits Application.Screenupdating = True