Add-in version overview

Introduction

xlOptimizer is a generic optimization tool that uses Microsoft Excel as a platform for the definition of the problem at hand. Practically any problem that can be formulated in a spreadsheet can be tackled by this program. Examples include problems in finance, engineering, resource allocation, scheduling, manufacturing, route finding, job scheduling, etc.

xlOptimizer differs radically from the built-in "solver" pack of Microsoft Excel in the sense that it implements a host of customizable, state-of-the-art metaheuristic algorithms (including permutation and multi-objective), homogenized and arranged in an intuitive interface. These algorithms are considered to be very good "global optimizers", i.e. they are able to find very good solutions (from a practical point of view) with few function evaluations. This renders them ideal for very difficult (multi-parametric, non-differentiable, discontinuous, combinatorial, deceptive, etc) and/or expensive problems (i.e. each function evaluation may require several minutes).

Apart from the built-in functions of Microsoft Excel, which are readily available, external functions can be easily implemented through dynamic-link-libraries (dlls), so that virtually any problem can be formulated efficiently within a spreadsheet. 

Add-in version

The add-in version of xlOptimizer has been developed using Microsoft's VSTO technology (Visual Studio Tools for Office) for both 32-bit and 64-bit versions of Microsoft Excel 2016 or newer. It will also work with Excel 365 (both 32- and 64-bit). It is installed in Excel's ribbon and does not require external programs or servers.

xloptimizer add-in toolbar

VSTO offers significant advantages:

  • Performance: the add-in is ~30x faster than the stand-alone version in problems with many design variables, as there is no requirement for data transfer between the server and the external program.
  • Convenience: the optimization data and settings are stored in a hidden sheet within the same workbook. There is no need to keep a separate file.
  • Flexibility: the cell references are now handled by Excel. This allows to cut and paste cells to other locations, insert or delete lines, etc without losing functionality in terms of optimization. In the stand-alone version, all affected cell references needed to be updated manually after each change in the workbook.

Features

The following algorithms are currently available for the add-in version:

Single-objective algorithms:

Multi-objective algorithms:

For more information on the program features, see here.

Projects

Certain applications of xlOptimizer add-in are included for demonstration purposes:

For a complete list of projects for the add-in version, see here.