Open solver

A tip of a great tool.

Open solver is an Excel addin. It is very similar to the MS solver. The difference is that the default solver has a limit of around 5000 variables. Above this, the user has to purchase the Frontline solver (the name of the company).


To use opensolver, just download, unpack and add it to the Excel supplements.

A good evolution of openSolver is solverStudio, from the same developer. It allows a formulation of the problem using formulas, supports more optimization languages and is faster. But it is also a bit more complex to deal with.

Solver studio

Both of these tools are able to use any solver. The package comes with CBC, the great open source solver from COIN-OR.

Frontline solver:

Ideias técnicas com uma pitada de filosofia

Indicator variable

An indicator variable can be useful in several cases.

Suppose we have the situation below.

Three areas, two normal ones, and one emergency area.

To use this emergency area, we need to pay a penalty to activate this area, besides the unitary cost.

Suppose we need a min value of 100.000 tons.

The question. How to model this in Linear integer programming?

Easy. With an indicator variable.

We model everything as usual.

But we add a binary variable (in B17), and a “Big M” calculation in B18.

The “big M” is a number great enough to our purposes.

If indicator is zero, 0*100000 = 0

If indicator is one, 1*100000 = 100000 > 40.000 (bigger than the emergency area).

In the constraints,

B17 = binary

B18 > B17, to force the indicator to be one if emergency area is used.

In Objective Function, we multiply the indicator and the penalty – if indicator is zero, no penalty, if it is one, add penalty.

For example, if the minimum volume is 100.000, then we do need the emergency area:

If the minimum volume is 74000, we don’t need to activate the emergency area.

File to download.

Ideias técnicas com uma pitada de filosofia: