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.
Great video once again !!!!! Just on a related note I once used binary in constraint when I had amounts and needed to choose some of them which will add up to a particular total. So kind of accounting reconciliation. I had maybe like 200 amount but it was already quite time consuming calculation for excel
Hey, this video inspired a solution to a problem a colleague was seeking my help with. It might be an idea for one of your EMT videos. He had some decision variables, for each variable there was a different list of options available, with respective different costs. To minimize the total cost, he wanted Solver to choose from a specific list of text values for each of the decision variables. This cannot be directly told to solver because it won't accept multiple RHS values to choose from for a constraint. So for each of his original decision variables, I went to the list of options and put beside it a list of new binary decision variables with the constraint that they have to add up to one (in order to force it to make only one of them 1 and the rest zeros). Then I turned his original decision variable cells into lookup functions to take the value in front of the 1. If it's difficult to imagine this from what I wrote I'd be happy to provide a workbook as a way of giving back =) Best, Ibrahim
That is a very clever solution!!! I probably will not make a video about anytime soon as I do not have plans for simulation video and have the next 1/2 year pretty much booked. But thanks for sharing : )
Hi, How can I do project optimization problem when projects are subject to total sum constraint that can be invested? Binaries do not work because I am allowed to use between 0 and 100 % of project (i) cost. Also, when I post constraints for dummies that have to be more or equal to 0 and less or equal to 1, solver cannot find a feasible solution. Please help. Thanks! P.S. Sorry it actually works :D
+Jennifer Heinz Then just like the 4 other earlier videos, we enter each objective function, operator and right-hand constraint individually. Of the 6 videos that show Liner Programming and Excel Solver in this series, 4 of them show entering constraints individually and 2 show how to enter multiple constraints when the comparative operator is the same for all.
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.
Thank you a million times!
You are welcome a million times : )
Great video once again !!!!!
Just on a related note
I once used binary in constraint when I had amounts and needed to choose some of them which will add up to a particular total. So kind of accounting reconciliation. I had maybe like 200 amount but it was already quite time consuming calculation for excel
+Tomas Hujo , What a great example!!! But yes, sometimes what we try and ask Excel to do for us takes a LONG time!
Hey, this video inspired a solution to a problem a colleague was seeking my help with. It might be an idea for one of your EMT videos.
He had some decision variables, for each variable there was a different list of options available, with respective different costs. To minimize the total cost, he wanted Solver to choose from a specific list of text values for each of the decision variables. This cannot be directly told to solver because it won't accept multiple RHS values to choose from for a constraint. So for each of his original decision variables, I went to the list of options and put beside it a list of new binary decision variables with the constraint that they have to add up to one (in order to force it to make only one of them 1 and the rest zeros). Then I turned his original decision variable cells into lookup functions to take the value in front of the 1.
If it's difficult to imagine this from what I wrote I'd be happy to provide a workbook as a way of giving back =)
Best,
Ibrahim
That is a very clever solution!!! I probably will not make a video about anytime soon as I do not have plans for simulation video and have the next 1/2 year pretty much booked. But thanks for sharing : )
You have EMTs for six months ahead?!!! =O
Not exactly, but I do have long term plans for a Data Analysis series with DAX and Power Query and other work related tasks that I am months behind...
All the best! Is this new series going to be a separate BUSN class?
hey if you can provide solution/workbook i will be thankful to you...
Was that fun?
Binary answer only!
:) :)
+pmsocho :) :) :)!!!!!!!!
Hi,
How can I do project optimization problem when projects are subject to total sum constraint that can be invested?
Binaries do not work because I am allowed to use between 0 and 100 % of project (i) cost.
Also, when I post constraints for dummies that have to be more or equal to 0 and less or equal to 1, solver cannot find a feasible solution.
Please help. Thanks!
P.S. Sorry it actually works :D
nice spreadsheet!
Our teacher just taught us the binary methods way harder than this. Hope you will come here to save our life lol.
Boom
thanks... very useful.
The formula for npv =NPV(B4,B8:B12) is WRONG. It should be =NPV(B4,B9:B12)+B8
the link is not reachable. tnx
Hello are you there I really need your help.
what if the operator value was different for the 4 projects?
+Jennifer Heinz Then just like the 4 other earlier videos, we enter each objective function, operator and right-hand constraint individually. Of the 6 videos that show Liner Programming and Excel Solver in this series, 4 of them show entering constraints individually and 2 show how to enter multiple constraints when the comparative operator is the same for all.
BOOM