Minimise an Excel Formula
Answered
Hi,
I have an excel formula to minimise in a non-excel environment. We are replacing excel with an ordinary excel reader on linux called Gembox. We hope to replace the macros and their associated solvers in excel with functions running in C#/C++/Python etc.
Previously excel's GRG solver algorithm would take care of minimising certain cells.
Minimize cell O17
O16 = Z4 + Z5 + SUM( Xi * Yi) , where i varies from 7 to 70.
by varying cell Z5 and cell O18
subject to two constraints
=INDEX(AC10:AC14,MATCH(O10,V4:V14))
and
=INDEX(AI4:AI14,MATCH(O10,V4:V14))
I have no clue where to even start on how to model this to gurobi.
What I think is really happening in excel is that Gradient Descent first puts in values for Z5 and O15 and then does a calculate on the excel worksheet and finds the value for O16. Going down the slope would mean putting values of Z5 and O15 so that the new O16 is lesser than the previous O16. And this process would go on till the bottom of the slope is reached . It does not have to be the global minimum. As is, GRG only really solves for one of the minimums in a reasonable constraint of time.
thank you,
Sunu
-
Official comment
-
Gurobi Staff
This post is more than three years old. Some information may not be up to date. For current information, please check the Gurobi Documentation or Knowledge Base. If you need more help, please create a new post in the community forum. Or why not try our AI Gurobot?. -
-
-
Hi Sunu,
What you should try to do first, is to write down the model you are minimizing in Excel on a sheet of paper, i.e., write it in the form
\[\begin{align}
\min_x\,\, &c^T x\\
\text{s.t.}\,\, & Ax \leq b
\end{align}\]If the problem is nonlinear then just use \(f(x)\) instead of \(Ax\). Once you were able to write your model in the above form, you should have a look at our Python webinar and our Python examples to write your model in a Gurobi readable way.
Please note that Frontline's GRG algorithm accepts any nonlinear terms while Gurobi only accepts bilinear terms \(x^2\) and \(x\cdot y\). Other nonlinear terms can be modeled via a piecewise approximation.
Best regards,
Jaromił0 -
Post is closed for comments.
Comments
2 comments