12
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 the application will come to you in either an XLSX or CSV file. On the flip side, when it comes time to convey the solution provided by your application, it is ...
8
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 fit in Excel, but the dimensions of the problem might be a bit much for the version of Solver included in Excel. There are some solver alternatives for Excel (...
7
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 the Excel solver.
Moreover Excel is a common tool when companies choose plugin optimization instead of packages, custom or tailored optimization.
7
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:
\begin{align}
y_1 = 1 &\implies x \in [0,A] \\
y_2 = 1 &\implies x \in [A,B] \\
y_3 = 1 &\implies x \in [B,C] \\
\end{align}
6
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 problem is quite small), it adds some "bells and whistles" including a model visualization component.
5
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 satisfied. Another important thing is, which kind of scheduling model (parallel scheduling, job shop, etc.) you have faced in your machinery area. To achieve your ...
5
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 how to do this with SOS2. As to how accurate it is, your mileage will vary.
The version of Solver in Excel is fairly limited. You might want to consider the ...
4
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, those are the constraints to fix. If not, meaning all constraints are satisfied, then possibly there is an issue with how the model was plugged into Solver.
4
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}
\max \quad & z = & 3x_1 & + & 2 x_2 & & & &&\\
\mbox{s.t.} \quad & & x_1 & + &...
4
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 way (with proper values see comments). After that you want to use sensitivity analysis to access reduced costs. Find a tutorial here.
4
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 solution, however, the problem size is somewhat limited and all of the constraints as well as the objective function have to be linear.
Your problem is small, ...
4
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.
parameter results(t,i);
loop(t,
* calculate y.l(i)
solve ...;
results(t,i) = y.l(i);
);
execute_unload "AllResults.gdx", results;
execute 'gdxxrw.exe AllResults.gdx par=results';
Calls to Excel are expensive so they should ...
3
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. That means that your primal solution is degenerate, and your dual problem has multiple optima. The highlighted difference probably means that the simplex solver ...
2
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 is intended to do relative to the Reference table. But your formula =((SUM(B19:F31))^2)^(1/2) makes no sense. Perhaps you intend to sum the squared differences ...
2
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 works without spending a small fortune in the process.
Speaking of the process, it is often useful to apply the Theory of Constraints to your business, and use it ...
2
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 nature. Lot of folks have given good advice above wrt modeling. I would suggest seeing if you can mimic the intuitive method used to create a simple constructive ...
1
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 building was initially fast.
1
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 like excel.
The second, by using a low-level API like C/C++, python, etc. I think you could write your own model in your favorite language through the many ...
1
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 objective is to maximize $\sum_i z_i$.
1
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 solution.
In theory, you write it as a system of equations and solve for the solution vector.
Example:
We have a budget $b_{max}$ and the sum of costs should not ...
1
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 here in case it’s useful to anyone.
I think this problem can be reformulated in a much simpler way. You are trying to
$$\text{maximize} \quad \sum_i \left[ \...
Only top voted, non community-wiki answers of a minimum length are eligible