13
votes
Accepted
Softwares for Operations Research. Is Excel Outdated or Useless for the OR industry?
Excel remains extensively used in industry for non-OR applications. That means that if you are doing an OR application that does not require access to a database, there's a good chance the data for ...
- 28.7k
8
votes
Accepted
If-Then-Else modeling in MILP using the Big M method
There is some ambiguity about the strictness of above/beneath, but does the following do what you want?
$$
0y_1 + Ay_2 + By_3 \le x \le Ay_1 + By_2 + Cy_3 \\
y_1 + y_2 + y_3 = 1
$$
Checking, we have:
\...
- 21.7k
8
votes
Job shop scheduling advice
It should be possible to model the production process using an integer or mixed-integer linear program. There is a lot of literature out there about MIP models for job shop scheduling. The data would ...
- 28.7k
8
votes
Softwares for Operations Research. Is Excel Outdated or Useless for the OR industry?
Many many people know Excel and use Excel. So many OR projects start with some Excel spreadsheet. And that is why being able to read from and write to Excel is key. You may even start the project with ...
- 3,337
7
votes
Accepted
Linear Programming: Integer and non-integer decision variables
For the first question, suppose you somehow knew which days would be production days and fixed the values of the $z_t$ variables accordingly, while allowing $q_t$ and $i_t$ to be continuous variables. ...
- 28.7k
7
votes
Python to Excel
A great option is to use the pandas library, and specifically pandas.DataFrame.to_excel is what you need.
I suggest you use one column per variable index, and one line per non zero variable.
Here is ...
- 10k
6
votes
Iterative calculus with Excel
What you are looking for is the Solver component of Excel. If you are willing and able to use plugins, I can recommend OpenSolver. In addition to a more powerful solver (which you won't need -- your ...
- 28.7k
5
votes
How can I maximize the sum of norm.dist values using solver?
You can approximate any nonlinear function via a piecewise-linear function using binary variables (or, if your solver supports them, type 2 special ordered sets). I have an old blog post explaining ...
- 28.7k
5
votes
Accepted
Job shop scheduling advice
The scheduling models in the real application are a bit different from the academic problems. For example, in a real situation, there are multi objectives/complicated constraints that should be ...
- 5,705
4
votes
It takes a long time to solve excel gurobi-opensolver - is this normal?
This is not a Gurobi issue - the log shows that the instance is solved within a second. I can only assume that the model-building process takes up a lot of time. OpenSolver apparently creates an LP ...
- 1,493
4
votes
Python to Excel
I have used openpyxl several times to create or alter excel files. It is quite easy if one is familiar with Python.
With this module you can iterate through your variables and write its value to a ...
- 389
4
votes
Problem solving a linear program using Excel
Try to come up with a feasible (not necessarily optimal) solution, plug it into cells F2 to F13, and see if any of your constraints are violated. If so, and assuming your solution is really feasible, ...
- 28.7k
4
votes
Accepted
Graphical illustration Excel
I'm not sure I understand your question as well but, let me describe an example using excel. In the following tiny instance, the model has two variables and four constraints.
\begin{alignat}{5}
\...
- 5,705
4
votes
Iterative calculus with Excel - N° 2
To do linear optimization in Excel you should use the built-in Excel Solver. Here is a good tutorial for installation and first steps.
It should be straight-forward to incorporate your problem this ...
- 1,313
4
votes
GAMS export to excel in a loop
It is much better to collect all y.l(i) in a parameter with an extra index t, and export that in one swoop. I.e.
...
- 1,431
4
votes
Accepted
Excel Solver is not performing as desired
There are different solvers in Excel that are suited for different types of problems:
Simplex LP
GRG Nonlinear
Evolutionary
The Simplex LP solver is the only solver that can guarantee an optimal ...
3
votes
Accepted
Why is the Lagrange Multiplier not equal the Shadow Price (Excel solver, Matlab linprog, Gurobi)?
If you look at the "allowable decrease" in the RHS of the highlighted constraint, it's zero. A number of the binding constraints have either allowable increase or allowable decrease zero. ...
- 28.7k
3
votes
Job shop scheduling advice
In the industry a lot of decision making is done via Excel based modeling. This is usually a start and some problems outgrow very quickly especially scheduling problems given their combinatorial ...
2
votes
Job shop scheduling advice
Don't get hung up on having to use Excel - many off-the-shelf software packages don't even begin to address the issues job shops have. Excel is a great way to experiment and develop a system that ...
- 21
2
votes
Accepted
Why are the final value and reduced cost 0 in excel sensitivity report for multiple variables even though there exists a unique optimal solution?
Consider the following LP:
\begin{align*}
\max\,3x_{1}+5x_{2}\\
\textrm{s.t. }x_{1}+2x_{2} & +s_{1}=3\\
2x_{1}+x_{2} & +s_{2}=3\\
x_{1}+x_{2} & +s_{3}=2\\
x,s & \ge0
\end{align*}
($s$ ...
- 28.7k
2
votes
Excel Solver: Solving matrix values to match row/column sum according to reference
The problem is feasible only if the sum of the rows equals the sum of the columns. That's true for your Reference table, but not for the other tables.
I'm not clear about what your objective function ...
- 248
2
votes
Accepted
How to formulate cumulative sum of LpVariable in Pulp Python
This is one way you can formulate/model the problem:
$$
\begin{align*}
x_{i,t} &\in R^{+} \text{ variable denoting amount of raw material $i$ processed/out in time $t$}\\
q_{i,t} &\in R^{+} \...
- 959
1
vote
Python to Excel
There are both pandas and openpyxl recommendations here. In my experience, if you want to go read and write on the cell level, or do more than basic analysis, use Pandas. Use openpyxl If you want to ...
- 11
1
vote
Accepted
Why does setting up a problem using Excel's OpenSolver slows down after 24,000 variables?
I managed to understand why the pace of building up the model using Open Solver slows down after a while.
The reason is because there were several (in)equations without values, which was why model ...
- 119
1
vote
Importing data into SCIP solver
As far as I know, SCIP has presented some useful solutions to deal with it.
First, by using an interactive shell, which I don't know has the capability to connect with the separate sheet software ...
- 5,705
1
vote
Help with excel solver
This is a maximum independent set problem on a graph with 12 nodes, which you can solve with one binary variable $z_i$ per node and one "conflict" constraint $z_i+z_j \le 1$ per edge. The ...
- 21.7k
1
vote
How would you linearize this scheduling problem? Or how would you solve this? It is variation of a set coverage problem for OpenSolver
In general, you could try defining the problem as a linear programming problem and then use the simplex algorithm or Gaussian elimination to find a solution or understand that there is no feasible ...
1
vote
How can I maximize the sum of norm.dist values using solver?
NOTE: This answer was written to respond to an earlier version of the question, which used a different form of the objective function. This answer is incorrect for the new version but I’m leaving it ...
- 12.5k
1
vote
Job shop scheduling advice
There are a lot of good answers above if you are willing to develop your own model and visualisations.
There’s also an app that I’d recommend: https://www.modelandoptimize.com
It has a simple ...
- 11
Only top scored, non community-wiki answers of a minimum length are eligible