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.
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...
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
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.
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.
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?
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.
@@MrXL Thanks for getting back to me MrExcel. I sent an email to support@solver.com 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!
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
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
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.
I found the answer of my problem Id been searching for it a long time, thank u so much sir.
best explanation ....hats off your effort..
Thankyou
bro !! this video is amazing .. I love you !!! you didn't miss anything
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.
Just what i needed, good job!
Amazing video.
Thank you so much.
Amazing!! Thank you!!
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...
thansk from Colombia
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
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.
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.
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?
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.
@@MrXL Thanks for getting back to me MrExcel. I sent an email to support@solver.com 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!
THANKS.. that was really helpfull...
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
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
My tools tab doesn't have "references" listed below it.. :(
I had the same, had to click stop (the box) and then click Tools and References should activated now
Bill Jelen for President!!
Thank you.
+Kaiser you are welcome!
I have many target in every cell and need to calculate from a column with random number
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.
Hi Bill, great video. Do you have a solution for "the solver" problem for mac users?
Just great, thanks Bill