Absolutely superb presentation. You are the Go-To source for clear explanations and pragmatic techniques for addressing optimization problems! I do have a question though... using Opensolver there is no separate option for "Solving for Integer Constrains" as there is with the built-in Excel Solver. For simple cases like this example both solvers return identical responses. Are there going to be potential trouble spots when using Opensolver for larger/more complex problems involving integer constraints?
Thank you so much for your kind words! I'm not sure what you mean by there not being an option to "solve for integer constraints" in open solver. Both the built-in Excel Solver and OpenSolver allow you to constrain some (or all) of the variables to be integer. OpenSolver is much more suited (and robust) for larger/more complex problems than the built-in Solver.
@@TallysYunes Didn't have any problems with OpenSolver. It's just that in this video tutorial you were doing things in the Solver Options like specifying requiring an exact Integer precision; there seemed to be no equivalent option setting in OpenSolver. I suspect none is needed (specifying Int in the constraint was sufficient). I thought I'd pop the question and you addressed it. Thank you. Once again, your video tutorials on these kinds of topics are always spot on.
I see what you mean now. The options available for the built-in Solver are also available in OpenSolver. Just click the arrow pointing down next to where it says "model" (to the left of the colored "solve" icon), and you'll see a menu. One of the choices is "Options..."
@@TallysYunes I see that now. In your video you set the Integer Optimality (%). What would be the equivalent setting in the OpenSolver - Solve Options?
When i use simplex LP in solver, i get 13 Large and 1006950 cost base. But when i use GRG non-linear i get the same answer as you. Do you have any idea as to why that is?
It could be a couple of reasons. Firstly, you should not be using GRG nonlinear for this problem because it's a linear problem and you want to find the optimal solution. Because we set the room counts as integer variables, check inside Solver 'Options' that your integer optimality % parameter is set to zero (also make sure that the 'ignore integer constraints' box is unchecked). Finally, because this problem has a mixture of large and small numbers, check the box 'Use Automatic Scaling' inside options as well. This should fix your problem.
When the data in the problem includes both very large and very small numbers (e.g. this problem has a 5 and a 1 million) there's a higher chance the calculations that take place behind the scenes will generate a larger error factor (computers don't calculate exactly; they're always rounding to some level of accuracy). This setting re-scales some of the numbers so that the difference between largest and smallest number isn't too great. As a consequence, there are fewer errors and you converge to the correct solution. Excel Solver isn't what one would consider to be a state-of-the-art optimization solver in the market, therefore it's more susceptible to these problems. A high-end solver like Gurobi, CPLEX, or FICO Express would easily handle this problem without the user having to worry about scaling the numbers.
I know this technique as just "Goal Programming." I don't know if it's attributed to Chebyshev, or if there exists something else out there known as "Chebyshev Goal Programming."
Say there was a specific scenario where you wanted to target 0 small rooms but wanted to keep it built into the overall solver functionality, how would you handle the div0 error?
Then, for the small rooms, you'd not divide by the target and only use the weight factor to control that deviation. It's not ideal, but it is what it is.
Very organized and easy to understand, thank you. I was wondering why you multiplied the weights by the percentage deviations and not the actual overage and underage values for the objective equation?
The reason why percentage deviations are needed is explained at time 17 minutes and zero seconds, when I go over two issues: unit mismatch and magnitudes of the deviations.
This is so good. I've been looking for tutorials for a week now. Thank youuu 🥺🥺🥺
You're very welcome! Glad to hear it was helpful.
Wow! where when you when I was studying this in college. You made it look so easy
Absolutely superb presentation. You are the Go-To source for clear explanations and pragmatic techniques for addressing optimization problems!
I do have a question though... using Opensolver there is no separate option for "Solving for Integer Constrains" as there is with the built-in Excel Solver. For simple cases like this example both solvers return identical responses. Are there going to be potential trouble spots when using Opensolver for larger/more complex problems involving integer constraints?
Thank you so much for your kind words! I'm not sure what you mean by there not being an option to "solve for integer constraints" in open solver. Both the built-in Excel Solver and OpenSolver allow you to constrain some (or all) of the variables to be integer. OpenSolver is much more suited (and robust) for larger/more complex problems than the built-in Solver.
@@TallysYunes Didn't have any problems with OpenSolver. It's just that in this video tutorial you were doing things in the Solver Options like specifying requiring an exact Integer precision; there seemed to be no equivalent option setting in OpenSolver. I suspect none is needed (specifying Int in the constraint was sufficient). I thought I'd pop the question and you addressed it. Thank you.
Once again, your video tutorials on these kinds of topics are always spot on.
I see what you mean now. The options available for the built-in Solver are also available in OpenSolver. Just click the arrow pointing down next to where it says "model" (to the left of the colored "solve" icon), and you'll see a menu. One of the choices is "Options..."
@@TallysYunes I see that now. In your video you set the Integer Optimality (%). What would be the equivalent setting in the OpenSolver - Solve Options?
In OpenSolver this is called "Branch and Bound Tolerance (%)"
When i use simplex LP in solver, i get 13 Large and 1006950 cost base. But when i use GRG non-linear i get the same answer as you. Do you have any idea as to why that is?
It could be a couple of reasons. Firstly, you should not be using GRG nonlinear for this problem because it's a linear problem and you want to find the optimal solution. Because we set the room counts as integer variables, check inside Solver 'Options' that your integer optimality % parameter is set to zero (also make sure that the 'ignore integer constraints' box is unchecked). Finally, because this problem has a mixture of large and small numbers, check the box 'Use Automatic Scaling' inside options as well. This should fix your problem.
@@TallysYunes Thank you so much Tallys, the "use automatic scaling" fixed the problem. What does this option do exactly?
When the data in the problem includes both very large and very small numbers (e.g. this problem has a 5 and a 1 million) there's a higher chance the calculations that take place behind the scenes will generate a larger error factor (computers don't calculate exactly; they're always rounding to some level of accuracy). This setting re-scales some of the numbers so that the difference between largest and smallest number isn't too great. As a consequence, there are fewer errors and you converge to the correct solution. Excel Solver isn't what one would consider to be a state-of-the-art optimization solver in the market, therefore it's more susceptible to these problems. A high-end solver like Gurobi, CPLEX, or FICO Express would easily handle this problem without the user having to worry about scaling the numbers.
@@TallysYunes Is this Chebyshev goal programming method? reply ASAP if possible
I know this technique as just "Goal Programming." I don't know if it's attributed to Chebyshev, or if there exists something else out there known as "Chebyshev Goal Programming."
Say there was a specific scenario where you wanted to target 0 small rooms but wanted to keep it built into the overall solver functionality, how would you handle the div0 error?
Then, for the small rooms, you'd not divide by the target and only use the weight factor to control that deviation. It's not ideal, but it is what it is.
Very organized and easy to understand, thank you. I was wondering why you multiplied the weights by the percentage deviations and not the actual overage and underage values for the objective equation?
The reason why percentage deviations are needed is explained at time 17 minutes and zero seconds, when I go over two issues: unit mismatch and magnitudes of the deviations.