Hot answers tagged

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 ...
prubin's user avatar
  • 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: \...
RobPratt's user avatar
  • 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 ...
prubin's user avatar
  • 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 ...
Alex Fleischer's user avatar
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. ...
prubin's user avatar
  • 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 ...
Kuifje's user avatar
  • 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 ...
prubin's user avatar
  • 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 ...
prubin's user avatar
  • 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 ...
mattmilten's user avatar
  • 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 ...
gmn's user avatar
  • 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, ...
prubin's user avatar
  • 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} \...
A.Omidi's user avatar
  • 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 ...
CMichael's user avatar
  • 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 ...
A.Omidi's user avatar
  • 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. ...
Erwin Kalvelagen's user avatar
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 ...
Walter Sebastian Gisler's user avatar
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. ...
prubin's user avatar
  • 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 ...
Srini_Nandiraju's user avatar
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), "...
LINDO Systems's user avatar
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 ...
A.Omidi's user avatar
  • 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 ...
Jason's user avatar
  • 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$ ...
prubin's user avatar
  • 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 ...
Solver Max's user avatar
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^{+} \...
anjikum's user avatar
  • 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 ...
prubin's user avatar
  • 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 ...
Sutanu Majumdar's user avatar
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-...
A.Omidi's user avatar
  • 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 ...
marco tognoli's user avatar
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 ...
A.Omidi's user avatar
  • 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 ...
Sutanu Majumdar's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible