I'm struggling with my qualification work. Excel methods solve the math problem easy, but I don't catch the idea how he does it. I need to understand it to create the code or just to describe the algorithm with help of block-diagrams.
The issue is that I have a mix of continuous function and discrete vars.
I attach two files: the first contains the function which is need to be minimized and constraints, the second one is excel file with the solution.
You can use the visual basic programming in Excel to write code to solve your problem
GRG stands for “Generalized Reduced Gradient”. In its most basic form, this solver method looks at the gradient or slope of the objective function as the input values (or decision variables) change and determines that it has reached an optimum solution when the partial derivatives equal zero.
GRG non-linear solver is based on work published by L. Lasdon in 1973 and Alan Waren and their 1975 published code implementation.
At best, the GRG Solving method alone – like virtually all “classical” nonlinear optimization algorithms – can find a locally optimal solution to a reasonably well-scaled, non-convex model. At times, Solver will stop before finding a locally optimal solution, when it is making very slow progress (the objective function is changing very little from one trial solution to another) or for other reasons.
When the message “Solver found a solution” appears, it means that the GRG method has found a locally optimal solution – there is no other set of values for the decision variables close to the current values that yields a better value for the objective function. This means that Solver has found a “valley” (if minimizing) – but if the model is non-convex, there may be other deeper valleys far away from the current solution.
Also, in your Excel file with the solution решение.xlsx there is a Misprint: $B$3>=0.72 rather than correct $B$3
Anastasia Kosyakina , I think that your problem may be reformulated as a mixed-binary linear problem (but not necessarily).
However, I need some more explanations.
1. Is the 7th constraint (the one with the sum from i=1 to m) the one in cell G12?
2. Where in the Excel file the formula beginning with -0,06Tout
3. What does k=1,p stand for? I guess it is some index but what is p then, and how is it reflected in Excel?
4. Alexander Kolker is right about the mistyped constraint.
Anas Mahmood Al-Juboori, yes, I can. That's one of possibilities, but still I want to understand the way the issue is solved.
Alexander Kolker, thank you for the information and error detection!
Przemysław Kowalik, your first assumption is right. What about the next points, I used Excel just to understand can it be solved at all, so there is only one value for Qsp in this file and it is the constraint for G12. It is supposed to use that function to recalculate the solution depends on the temperature outside and then the Qsp_k would be the pool of possible values (about 37 different numbers, that would be p=37)
Sorry, it doesn't give me hints how to mention people.
Anastasia Kosyakina
1. OK, so you must in fact solve 37 "variants" of the problem for any of the values k=1,2,...37 - am I right?
2. Please find attached your file with two versions of the problem revisited.
The first one is your version slightly corrected. In G12 used the SUMPRODUCT function instead of "direct" multiplication and division. I also "extracted" paramteres of constraints from the Solver settings to cells - it made entering constraints in Solver simpler and more error-proof.
The second one is a linear version solved with the simplex method.
PS. I can read Russian a little, but I wouldn't dare typing, especially with the Latin-only keyboard so I put comments in the file in English. I had also to change the filename because my browser couldn't save Russian letters (it left the extension only :)).
You mention people in RG by typing @ and then selecting a person from prompted proposition or type the RG username by yourself.
Przemysław Kowalik, you are a genius O_O
Now it looks a lot easier, what's more, I understand the solution method. As for me, that's impossible brilliant decision with the M-parameter.
It really get me off the ground, thank you!
- Badges
- Science topic
- Similar topics
- Filing