Goal Programming: Soft Constraints with Excel Solver

Рет қаралды 9,571

4 жыл бұрын

Enjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoffee.com/tallysyunes
Buy complete spreadsheet: www.buymeacoffee.com/tallysyu...
Buy me an item from my wishlist: www.buymeacoffee.com/tallysyu...
Up to this point (if you're following my Excel Models playlist in the right order), the constraints we have implemented in optimization models have been "hard" in the sense that they must be obeyed. There are situations, however, when you are willing to violate a certain constraint (turn it from hard to "soft") as long as you don't violate it by too much (for example, you may be willing to go a bit over budget if that means finding a good solution to the problem at hand). Goal Programming is the tool that allows you to implement models with soft constraints in them, which is what we'll learn in this video.

Пікірлер: 17

@wadapak7622 3 жыл бұрын

Wow! where when you when I was studying this in college. You made it look so easy

@nichdaniel 3 жыл бұрын

This is so good. I've been looking for tutorials for a week now. Thank youuu 🥺🥺🥺

@TallysYunes 3 жыл бұрын

You're very welcome! Glad to hear it was helpful.

@evolvingtech1 Жыл бұрын

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?

@TallysYunes Жыл бұрын

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.

@evolvingtech1 Жыл бұрын

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

@TallysYunes Жыл бұрын

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

@evolvingtech1 Жыл бұрын

@@TallysYunes I see that now. In your video you set the Integer Optimality (%). What would be the equivalent setting in the OpenSolver - Solve Options?

@TallysYunes Жыл бұрын

In OpenSolver this is called "Branch and Bound Tolerance (%)"

@canureadman 2 жыл бұрын

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?

@TallysYunes 2 жыл бұрын

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.

@julianapendleton9271 3 жыл бұрын

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?

@TallysYunes 3 жыл бұрын

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.

@AdrianColumbus 3 жыл бұрын

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?

@TallysYunes 3 жыл бұрын

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.

@AdrianColumbus 3 жыл бұрын

@@TallysYunes Thank you so much Tallys, the "use automatic scaling" fixed the problem. What does this option do exactly?

@TallysYunes 3 жыл бұрын

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.