Evolutionary Spreadsheet Solver in Optimal Engineering Design

. Spreadsheet solver proved to be an excellent tool to solve operational research problems modelled as linear programming problems. Majority of engineering design problems are nonlinear in nature. The paper presents ability of spreadsheet solver to solve such problems as: four bar statically determinate truss, compound gear train problem and sequence determination problem by means of evolutionary engine.


Motivation and introductory remarks
Spreadsheet solver is widely used to solve linear programming problems [1]. Mathematical models of engineering design problems are in vast majority of cases nonlinear in nature. Some of them cannot be solved even by classical GRG Nonlinear solver. In the presented cases -four bar statically determinate truss, compound gear train problem and sequence determination problem -evolutionary solver should be used.

Basics of evolutionary (genetic) algorithm
A genetic or evolutionary algorithm applies the principles of evolution found in nature to the problem of finding an optimal solution. In this approach the problem is encoded in a series of bit strings that are manipulated by the algorithm. The decision variables and problem functions are used directly. An evolutionary algorithm for optimization is different from "classical" optimization methods in several ways. It uses such ideas as randomness, mutation, crossover and selection. A drawback of any evolutionary algorithm is that a solution is "better" only in comparison to other, presently known solutions.

Evolutionary solver parameters
All parameters of evolutionary solver are in spreadsheet located in one tab. In the Convergence box the maximum percentage difference in objective values for the top 99% of the population that Solver should allow in order to stop should be set. In the Mutation Rate box, a number between 0 and 1, the relative frequency with which some member of

Comparative study of sample calculations
In order to prove that evolutionary solver can be used to optimize engineering problems three known from literature examples were investigated: four bar statically determinate truss, compound gear train problem and sequence determination problem.

Four bar statically determinate truss
As first illustration a problem solved by Majd [2] and later by Haftka and Gurdal [3] is presented. The objective is the minimum weight design of the four bar statically determinate truss shown in Figure 1. Additionally, there are stress constraints in the members and a displacement constraint at the tip joint of the truss. In order to simplify the problem, it is assumed that members 1, 2 and 3 have the same cross-sectional area A 1 , and the member 4 the area A 2 . Under the specified load the member forces are described by (1) and the vertical displacement at the tip joint by (2).
Haftka and Gurdal [3] formulated this problem in terms of the non-dimensional variables (4). Objective function which is a separable nonlinear function was put by them in a piecewise linear form and the whole problem was solved by standard linear programming (LP) algorithm.
Ghasemi, Hinton and Wood [4] solved this problem for continuous variables using Genetic Algorithm (GA) and compared results with exact solution and results obtained by Sequential Quadratic Programming (SQP). Figure 2 shows computational model used in spreadsheet.

Fig. 2. Computational model for four bar statically determinate truss.
Comparison of results is in Table 1. Results obtained from calculations performed by solver and its evolutionary engine are exact. This problem can also be solved graphically using Haftka and Gurdel non-dimensional formulation. In this case goal function (5) is nonlinear and constraints (6) are linear.
After some calculations presented in (7) another formulation can be obtained which leads to graphical solution presented in Figure 3.

Compound gear train problem
The compound gear train problem was introduced by Sandgren [5]. Later it was solved by Kannan and Kramer [6]. They used an augmented Lagrange multiplier-based method for mixed integer discrete continuous optimization. The problem was also solved by Cai and Thierauff [7] and Pant, Thangram and Abraham [8] by means of particle swarm metaheuristics. The compound gear should be designed in such way that the gear ratio is as close as possible to 1/6.931. For each gear the number of teeth must be between 12 and 60, so all variables should be integer. T T subject to (9) 1 2 3 4 12 60, 1, 2,3, 4 , , , , , , integers where T a , T b , T d and T f are numbers of teeth on gears A, B, C and F respectively. Comparison of results is presented in Table 2.

Sequence determination problem
Let us consider the design of metal plate that have 10 bolts at locations shown in Figure 5. Bolts are to be inserted into the pre-drilled holes by a computer-controlled robot arm. The objective is to minimize the movement of the robot arm while it passes over and inserts a bolt into each hole. This problem was for the first time presented by Huang, Hsieh and Arora [9].   Computational model and results are presented in Figure 6.

Conclusions and final remarks
This paper proves that spreadsheet can be used not only for LP problems like cutting stock problem [1]. Evolutionary solver can also be used to solve engineering problems like optimal design. Solver has well known limitations imposed on number of decision variables and constraints. In such a case one can use external tool -Frontline Premium Solver.