# XLOPTIM® Help Center

For any questions relating to the use of the XLOPTIM® software, you can contact customer support at **support@xloptim.com**

## 1. About Operations research

We invite you to read our introduction to Operations Research, available on this site, in order to discover the discipline and become acquainted with the technical terms that will be used in this online help.

## 2. XLOPTIM® Approach

The XLOPTIM interface totally relies on Microsoft Excel, whether for inputting the data or for displaying the results. The computations, however, are completely independent of Excel and the corresponding programs have been developed in partnership with LocalSolver®.

In order to guarantee accurate results, the XLOPTIM software has been intensively tested and it has been validated by specialists of the statistical methods of interest.

Addinsoft has always been concerned about permanently improving the XLOPTIM software suite, and welcomes any remarks and improvements you might want to suggest. To contact Addinsoft, write to support@xloptim.com.

## 3. Data selection

A unique selection method is available:

**Selection by range**: you select with the mouse on the Excel sheet all the cells of the table that correspond to the selection field of the dialog box.

Notes:

Multiple selections are not possible.

You cannot use variables in multiple excel files. On the other hand, you can use data in several excel sheets.

Not all Excel functions can be used with variables, only those shown below are for the moment, some will be added in future versions of XLOPTIM®:

- SUM
- SUMPRODUCT
- MIN
- MAX
- TRANSPOSE
- MMULT
- MOD
- POWER
- PI
- SQRT
- LOG
- ABS
- CEILING
- SIN
- COS
- EXP
- TAN
- FLOOR
- OR
- XOR
- AND
- NOT

### Messages

XLOPTIM® uses an innovative message system to give information to the user and to report problems.

The dialog box below is an example of what happens when an active selection field (here the Dependent variables) has been activated but left empty. The software detects the problem and displays the message box.

The information displayed in red (or in blue depending on the severity) indicates which object/option/selection is responsible for the message. If you click on OK, the dialog box of the method that had just been activated is displayed again and the field corresponding to the Quantitative variable(s) is activated.

This message should be explicit enough to help you solve the problem by yourself. If a tutorial is available, the hyperlink https://www.xloptim.com links to a tutorial on the subject related to the problem. Sometimes an email address is displayed below the hyperlink to allow you send an email to Addinsoft using your usual email software, with the content of the XLOPTIM® message being automatically displayed in the email message.

## 4. Create your model XLOPTIM®

### Pose your problem

Step 1: Analyze the situation by finding the dimensions of the problem: namely the time indices, resources, location, ...

Step 2: Identify the data of the problem, that is to say all the information which cannot change during the optimization and which is in parameters of the problem.

Step 3: Identify and define the decision variables of the problem.

Step 4: Set the constraints of the problem.

Step 5: Write the objective of the problem, that is to say what we are trying to maximize (or minimize) in the problem.

### Setting your goal

To seek to optimize its objective function, the user must first enter a formula in a box. In the majority of cases, the variables are related to the objective function of the problem. This objective can for example represent a turnover to be maximized or sums of costs to be minimized.

If no variable is linked to the formula, the solver will simply take care of solving the system of constraint equations. The goal will then become to find a workable solution. In this case, the user will need to enter a constant numeric value in a cell (for example "0") and choose that cell from the selection.

### Adding decision variables

The use of the solver will inevitably require questions which the resolution will implicitly answer. Namely the result of the variables. A decision variable represents a value of the problem that we will be able to vary in order to change the value of the objective function.

The variables involved in the problems can be of different types.

To give an example in a knapsack problem where one decides or not to choose an object among others, the type of variables could be modeled by binary variables. In the case of a batch production problem where the quantities produced are unknown in advance, integer variables are used.

To be able to facilitate the modeling of a problem, we have provided an innovative interface with the possibility of assigning each group of variables a color in order to be able to better visualize them in the sheet.

**Please note, XLOPTIM® is limited to 1000 decision variables.**

#### Dialog boxes

This dialog box is made up of several boxes corresponding to the various useful parameters for adding or modifying a group of variables, available both for the management of the calculations and for the display of results.

Below is a description of the different elements of the dialog box:

The second concerns more specifically the parameters of the variables.

This dialog box is made up of several boxes corresponding to the various useful parameters for adding or modifying a group of variables, available both for the management of the calculations and for the display of results.

Below is a description of the different elements of the dialog box:

Name : Label assigned to the group of selected variables ;

Cell : Allows you to select the variables on the Excel sheet;

Type : The variables could be Boolean, whole or real. In this case, the type must be selected from this list;

Lower limits : You must specify the lower limit of the variables by going directly to find the cell containing the lower limit or by entering its value directly;

Upper limits : identical for the upper limits;

Colors : To allot a color to a range of variables, it is enough to click on the button, a dialog box appears then with a pallet of colors. One can then choose the color and that will put automatically up to date the Excel sheet.

### Adding a constraints

Each constraint added will make the problem at the same time more complex, and will reduce the search space for the solutions. For that, various manners of additions are possible.

Please note, XLOPTIM® has no limit on the number of defined constraints.

#### Dialog box

Concerning the addition of constraints, we placed at your disposal 2 dialog boxes. One of which relates to the addition, suppression as well as the modification of constraints:

The second more precisely relates to the parameters of the constraints:

This dialog box is made up of several boxes corresponding to the various parameters useful for the addition or the modification of a group of variables, available as well for the management of calculations as for the posting of the results.

You will find below the description of the various elements of the dialog box:

Left side: corresponds to the attributes of the constraint concerning the main or redundant decision variables.. You must select one or more cells containing the left part of one or more constraints. These cells may contain formulas with direct or indirect reference to the variables.

Operators: the type of constraint (equality = or inequality <=, <, > or >=)

Right side: you will be able to enter a numerical value or to select one or more cells containing the right part of one or more constraints.

### Exemples

#### Break-free constraints

That is to say X1 and X3 two Boolean variables which cannot be used together. In the knapsack problem, this could be similar to the fact that 2 items cannot be put in the same bag.. In this case, several modeling options are offered to you.

- Classic linear constraint:

$$x1+x3\leqslant1$$

In this case, you will first have to enter in a box of the worksheet the sum of the two variables and to add the constraint compared to constant 1.

- Nonlinear constraint:

$$XOR(x1=1;x3=1)=1$$

In this second example, you will have to first enter this formula into a box of the worksheet which expresses that one of the expressions must be true.

#### Capacity constraints

In certain problems, we can meet constraints which can be directly related to a capacity in size or limited weight. Dans un exemple où Pi represents the weight of each element i, Xi la variable booléenne indiquant le choix ou non de l'élément i et M la capacité, les contraintes peuvent être modélisées ainsi :

- Classic linear constraints :

$$\sum_{i=1}^npixi \leqslant M$$

To model this type of constraints you must first use the function SUMPRODUCT(P; X) with P the vector of weight and X the vector of variables.

#### Assignment constraints

To assign tasks to machines or agents requires using constraints known as assignments. Indeed, in certain cases the agent will be able to carry out only one task at the same time and the task could be allotted only to one agent.

Let's assume that there are as many agents as tasks. That is to say n the number of tasks Xij, the Boolean variable which is worth 1 if one allotted task i to the agent j, 0 if not.

- Traditional linear constraints:

$$\sum_{j=1}^nx_{ij}=1$$ $$\sum_{i=1}^nx_{ij}=1$$

- Non-linear constraints:

Another manner of proceeding would be to enter for each vector column the following formula $$XOR(x_{,J})=1$$ and for each line $$XOR(x_{,I})=1$$. All these formulas will have to be equal to 1.

## Options

You will be able to change some parameters of the solver by going to the "Options" tab:

Interval: Corresponds to the resolution time limit in seconds.

Iterations: Corresponds to the limit of the iteration count generated by the solvor.

## Optimization launch

Now that you grasp the whole of the characteristics of your optimization model, you will be able to launch your resolution. Do not forget that the number of decision variables is limited to 1000, without any limit on the number of constraints.

While pressing on the button LAUNCH, XLOPTIM® calls LocalSolver by transmitting to him the whole of information relating to your model (function objective, constraints, variables of decisions).

After having found an optimal solution to your problem, LocalSolver sends back the solutions to the interface which then updates the values of your decision variables, thus indirectly the value of the objective function related to these variables.

## How does the solver work?

LocalSolver® is a new kind of mathematical optimization solver.

LocalSolver® combines many different optimization techniques while needing no parameter tuning: local and direct search, constraint propagation and inference, linear and mixed-integer programming, as well as nonlinear programming techniques.

Contrary to other math optimization software, LocalSolver® is not based on a single optimization technique. It hybridizes different optimization techniques dynamically, during the resolution, thanks to a unique search approach. LocalSolver combines local and direct search techniques, constraint propagation and inference techniques, linear and mixed-integer programming techniques, as well as nonlinear programming techniques, to solve your problem at best.

Moreover, LocalSolver integrates unique heuristic search techniques for both combinatorial and continuous optimization. In this way, it is able to tackle models involving millions of variables, which are out of scope of classical solvers, especially classical mixed-integer linear programming (MIP), constraint programming (CP), or nonlinear programming (NLP) solvers.