A Rapid Optimization Prototyping Tool for Spreadsheet-Based Models

This paper presents an application of a rapid prototyping tool utilising Single-Objective and Multiple-Objective Genetic Algorithms (GA) to optimization problems defined using spreadsheets. The tool is implemented as an add-in for Microsoft Excel® which combines a robust and powerful C++ library of GAs with a familiar user interface allowing for a fast and easy definition of objective functions. The tool can also be easily linked with various hydraulic solvers (e.g. EPANET) using VBA and thus presents a great instrument for researchers and practitioners who want to solve complex optimization problems (e.g. design, rehabilitation and calibration of water distribution systems) with a minimum of additional programming. The capabilities of the tool are demonstrated on the Hanoi Water Distribution System design problem as a multiple-objective optimization and the performance in terms of quality of obtained solutions is compared with those obtained with a single-objective formulation.