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 ...
- 37.8k
10
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:
\...
- 30.5k
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 ...
- 37.8k
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,990
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. ...
- 37.8k
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 ...
- 12.9k
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 ...
- 37.8k
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 ...
- 37.8k
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,548
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 ...
- 622
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, ...
- 37.8k
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}
\...
- 8,440
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,333
4
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 ...
- 8,440
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.
...
- 2,585
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. ...
- 37.8k
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 ...
3
votes
Accepted
Can sensitivity analysis and limits report be done on an BILP problem?
Parametric/sensitivity analysis of a right hand side coefficient in an optimization problem with integer variables can be done, but it is messy. See:
Schrage, L, and L. Wolsey (1985), "...
- 509
3
votes
Container Loading Problem
As a free Excel plugin to solve a 3D packing problem and already with the visualization, the Vehicle Routing and Logistics Optimization within EURO, VeRoLog, has a material that may help to solve such ...
- 8,440
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$ ...
- 37.8k
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 ...
- 315
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^{+} \...
- 974
2
votes
Accepted
How to approach this shifts problem with Excel Solver?
I very much doubt that Solver knows how to convert an IF function into a linear constraint (although I suppose it is possible). So I think you need to rethink your MIP model.
If the variable limit in ...
- 37.8k
2
votes
How to approach this shifts problem with Excel Solver?
Well, to do min-max I think you need
Assuming project implies company
Minimize $z$
s.t.
$z \ge \sum_{i=1}^4 Y_{i,j}c_i \ \ \forall j$
$\sum_{j=1}^W Y_{i,j} = t_i \ \ \forall i$: where W = 9 (assigned ...
- 3,343
2
votes
How to approach this shifts problem with Excel Solver?
First of all, you will have to ensure that the total number of weeks is enough to schedule, otherwise, it causes many overlaps in projects. The mentioned problem can be formulated either as a resource-...
- 8,440
2
votes
How to approach this shifts problem with Excel Solver?
Introduction
The problem consists in determining the optimal project start-up sequence, i.e. the sequence such as to minimize the number of workers hired without violating the time constraint imposed ...
- 365
1
vote
Optimization of Weekly Productivity Goals
One possible way to achieve what you want is by modeling the problem as a Resource-Constrained project scheduling problem. In which the project plan is around $8$ weeks, and in each week you can ...
- 8,440
1
vote
Optimization of Weekly Productivity Goals
Assuming weekly_share = x[d] where d is the day of the week
you can add constraints:
y[d] * x[d] = 1.
Then replace weekly_share with y.
As for other constraints, solver will simply choose 10% for ...
- 3,343
Only top scored, non community-wiki answers of a minimum length are eligible