1/6

Application of MS-Excel “Solver” to Non-linear Beam Analysis
Toshimi Taki
March 4, 2007
April 28, 2007, Rev. A
1. Introduction
Spreadsheet software in current personal computers is high performance and the software has enoughfunctions for application to engineering problems. I developed a method to solve non-linear structuralproblems by optimization function “Solver” of MS-Excel.

2. MS-Excel “Solver”
The most popular spreadsheet software, MS-Excel has an add-in tool called “Solver” which performs
numerical optimization. “Solver” finds parameters to optimize “objective value” with multiple constraints.
We can use “Solver” to solve structural problems applying principle of total potential energy or
principle of total complimentary energy.

3. Equations – Non-linear Beam Element
Beam structure is discretized to elements as finite element method (FEM). Then, total potential energy
is calculated. “Shape function” is the same as FEM formulation. This means that the formulation is thesame as FEM, but we don’t need to perform variational operation. We just need to calculate strain energyof each beam element and sum up the strain energy for all the elements.
Following formulation show strain energy of 2-dimentional beam element.
s(1) Beam Element
Assume that properties of a beam element as follows.
Uniform cross section in a element. Section Area, Ae, Moment of Inertia, Ie, Length, LeYoung’s Modulus, Ee

As shown in figure 1, following symbols areθe1’=θe1 - φused.

ye’x, y deflection and rotation at Grid 1 in elementyecoordinates before deflection: (ue1, ve1, θe1)x, y deflection and rotation at Grid 2 in element
coordinates before deflection: (ue2, ve2, θe2)

(ue1, ve1)

ve2’=0Le’

θe2’=θe2 - φφ

θe1

θe2

Figure 1. Beam Element

Deflection of element, ve is expressed as follows.

(ue2, ve2)

ve1’=0Le(2) Bendingxe’xe

2/6ve ( xe ) = axe + bxe + cxe + d ,32dv e2= 3axe + 2bxe + c ,dx ed 2 vedxe

2= 6axe + 2b dve = θ e1 = cdx e  xe = 0

at x e = 0 ,

ve1 = d ,at x e = Le ,ve 2 = aLe + bLe + cLe + d ,32 dve 2= θ e 2 = 3aLe + 2bLe
 dxe  xe = Le

Solving the equations,a=θ e1 + θ e 2
Le

2−2(ve 2 − ve1 )
Le

3b=−,

c = θ e1 ,2θ e1+θ e 2 3(ve 2 − ve1 )
+2
Le
Le

d = ve1

Strain energy for bending is expressed as follows.
2E I LE I Le  d 2 ve  dxe = e e e (6axe + 2b )2 dxe = E e I e Le 6a 2 Le 2 + 6abLe + 2b 2Ub = e e ∫ 22 0  dxe 2 ∫0(To calculate shear force and bending moment, use following beam equations.
Shear force and bending moment at Grid 1, (V1, M1)
Shear force and bending moment at Grid 2, (V2, M2)
Equation of beam: E e I e

d 2 vedxe

2= −M eV1 = E e I e c , M 1 = − 2 E e I e b

V2 = − V1 ,M 2 = − M 1 + V1 L e(3) Extension
Length after deflection Le’ is expressed as follows.

(Le + u e 2 − u e1 )2 + (ve 2 − ve1 )2Le ' =Extension ∆Le is,∆Le =(Le + u e 2 − u e1 )2 + (ve 2 − ve1 )2 − Le

Strain energy Ue is,

Ue =EA 2∆L2 Le)3/6

To calculate axial force at Grid 1, Pe1 and Grid 2, Pe2 , use following equations.

Pe1 = − EA∆L,
LePe 2 = − Pe1(4) Consideration of Geometrical Non-linearity in Coordinate System after Deformation
Element coordinate system after deflection is considered as shown in figure 1. Element defections inelement coordinate system after deflection, ve1, ve2 become zero and strain energy for bending of theelement is expressed as follows.

a=θ e1 '+θ e 2 'Lewhere,2b=−,

θ e1 ' = θ e1 − φ ,2θ e1'+θ e 2 ',Lec = θ e1 ' ,d =0

θ e2 ' =θ e2 − φφ is angle between element coordinate after and before deflection.
Then, strain energy for bending (geometrical non-linearity is considered), Ub is expressed in thefollowing equation.

(U b = E e I e Le 6a 2 Le + 6abLe + 2b 22)(5) Total Potential Energy
Total potential energy of the element, Utotal is expressed as the following equation.

U total =∑ (U + U ) −eb

all elements∑ (P u + P v + M θ )x x
all external forces

yxzz

Using deflections and rotations at all grids as parameters, minimize total potential energy, Utotal by“Solver” of MS-Excel, then you will have the solution.

4. Example Problem – Elastica
Post-buckling deflection of a simply supported beam under axial force is analyzed. This problem is atypical geometrically non-linear problem known as “Elastica”. Analytical solution is available for“Elastica” and the result of the present method is compared with the analytical solution.
A beam with uniform cross section is divided to 20 elements as shown in figure 2. A template ofMS-Excel to calculate total potential energy was developed. Then, the total potential energy wasminimized by “Solver”.

4/6y, v

Element ID
12323 445678915 16 17 18 19 20 21

10 11 12 13 14Load

15678x, u

910 11 12 13 1415 16 17 18 19 20Grid ID
LFigure 2.

Model for Elastica

Figure 3 shows deflection shape for various axial forces.
Figure 4 shows relationship between center deflection and axial force. Center deflection and axial force
are normalized with beam length, L and buckling load, Pcr , respectively. Exact analytical solution isshown in the figure for comparison (reference [1]).

0.6

0.5

0.4

y/L

0.3

0.2

0.1

0-0.1

-0.2
-0.2

-0.1

0

0.1

0.2

0.3

0.4

0.5

0.6

x/L

Figure 3. Deflection Shape

0.7

0.8

0.9

1

1.1

1.2

5/6

2.2
Exact Solution

2.1

Proposed Method

2
1.9
1.8

P/Pcr

1.7
1.6
1.5
1.4
1.3
1.2
1.1
10

0.1

0.2

0.30.40.5

0.6

vmax/L
Figure 4. Relationship between Center Deflection and Axial Force

5. Other Applications
The method I introduced in this paper can be applied to following problems in aircraft structuralanalysis. I developed the method 10 years ago and have applied to many problems of actual aircraft
structural analysis.
Truss (linear, or geometrically non-linear)Beam and Ramen (linear, or geometrically non-linear)Bolted Joint (linear)Beam Column (geometrically non-linear)2-dimensional Elastic Problems (linear, or geometrically non-linear)Shear Field Problems (linear)6/6

6. Conclusions
The method presented in this paper has following features and I expect that the method will be widelyused in the aircraft industry.
Straight forward method. Special technique and tedious deviation of equations are not necessary tosolve non-linear problems.

No special programs are required. General purpose spreadsheet software is used.

Applicable to actual aircraft structural analysis.



Useful to education of energy method.

7. References[1] C. L. Dym and I. H. Shames, “Solid Mechanics, A Variational Approach”