Lublin University of Technology
Question
Asked 13th Mar, 2020
How does the Excel work with GRG algorithm?
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.
Most recent answer
Anastasia Kosyakina You are welcome :)
In fact, using a "big M" (eg. "sufficiently large" positive parameter) is a standard trick in optimization models (not only in connection with binary variables).
Popular answers (1)
General Electric
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<=0.72
4 Recommendations
All Answers (7)
General Electric
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<=0.72
4 Recommendations
Lublin University of Technology
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.
National University of Science and Technology MISIS
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.
Lublin University of Technology
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.
National University of Science and Technology MISIS
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!
Lublin University of Technology
Anastasia Kosyakina You are welcome :)
In fact, using a "big M" (eg. "sufficiently large" positive parameter) is a standard trick in optimization models (not only in connection with binary variables).
Similar questions and discussions
What is InTech Open Science? A predatory or a ligitimate publisher?
Dear friends colleagues, have you ever received an invitation to publish your work at InTech Open Science (https://www.intechopen.com/)? I have recently been invited to edit a new book title for them. I am usually suspicious with such invitations and must check the authenticity of the publisher first. Interestingly, they claim that they have published the work for two recent Nobel Laureates. Therefore, I would appreciate your experience and opinions regarding InTech Open Science.
Kind regards,
International Conference on Nonlinear Analysis and Applications (ICNAA 2024) & Symposium on Ancient Indian Mathematics
Dear colleagues
I am pleased to inform you that the International Conference on Nonlinear Analysis and Applications (ICNAA 2024) & Symposium on Ancient Indian Mathematics (in the memory of Late Professor S. L. Singh) is being organized by the Department of Mathematics, Pt. L. M. S. Campus, Sridev Suman Uttarakhand University, Rishikesh-249201, Uttarakhand, India from May 10 to May 12, 2024 (https://icnaaa2024.wordpress.com).
Your active participation in the conference will undoubtedly contribute to the success of the event.Kindly submit the abstract of your talk at your earliest convenience.
Link to Registration:https://forms.gle/8LEY9KECHHKqTrPq8
Looking forward to your valuable contribution.
Best Regards
Anita Tomar
Related Publications
This Brief offers a comprehensive study covering the different aspects of gas allocation optimization in petroleum engineering. It contains different methods of defining the fitness function, dealing with constraints and selecting the optimizer; in each chapter a detailed literature review is included which covers older and important studies as wel...
In this article, the dynamic behaviour of rotor-bearing system is evaluated with particular environment and the newest optimization techniques, i.e. artificial rabbit’s optimization and dynamic arithmetic optimization algorithm are employed to reduce the system’s vibration amplitude. Designing and refining of system components are followed by error...