Share this: Google+
Eigen value in Spreadsheet
Eigen value is very important for many applications in Science and Engineering such as solution to linear and nonlinear differential equations, boundary value problem, chaos theory, discrete dynamical system, Markov chain, network analysis, Population growth model, are just a few applications to mentions. Eigen value of symmetric matrix appears to be very useful for many practical problems. Many statistical and decisionmaking tools such as multidimensional scaling (MDS) and Principle component Analysis (PCA) are rely heavily on the finding of Eigen value of symmetric matrix of covariance or correlation matrix.
Many people believe that finding Eigen value is very difficult problem. In this tutorial, we will learn an easy way to find Eigen value of a symmetric matrix using MS Excel Goal seeks. In principle, finding Eigen value is the same problem as finding a root of polynomial equation.
Symmetric matrix has special properties that the Eigen values are always real number (not complex number). That is why we can easily solve using
MS excel Goal seeks
. You can use the procedure below to find the Eigen value of any square matrix, but some of the complex Eigen value may never be found.
Click here for explanation on how to get symmetric matrix from any matrix
.
Suppose we have a symmetric , and we want to find the Eigen value of it. The procedure is back to the definition of Eigen value as the root of characteristic equation , where is identity matrix and is the Eigen vector of matrix . The characteristic equation can be solved if the determinant of the first term is equal to zero, or . This is exactly what we are going to do in the following steps to find the Eigen value of symmetric matrix . You may download the spreadsheet in here .

Suppose we have the following symmetric matrix as shown in the figure on the right. Optionally, you can give name to the matrix using menu InsertNameDefine matrix_A , then press OK button
 Type the identity matrix , which has the same size of matrix . Name it as matrix_I
 In an empty cell type any number as the first guess of Eigen value, and name it lambda

In a new empty cell, type this formula = matrix_A  lambda * matrix_I . Hightlight 3 by 3 cells down and to the right starting from this cell, press F2 and then press CTRLSHIFTENTER together. What you get is matrix . Name this matrix_A_lamdba_I
 In another cell, type = MDETERM( matrix_A_lamdba_I ) and press Enter
 From the menu of MS Excel, click ToolsGoal Seek and Goal Seek dialog show up.
 Set cell contain determinant formula to value 0 ( target value ) by changing cell that contain initial guess of lambda and click OK button. After a few iterations, you get the Eigen value. The correct Eigen value of the matrix in this example is 0.9233, 1 and 27.0767
Check this
blog
for your possible problem
See also:
Goal Seek Tutorial
,
MS Excel Tutorial index
,
Kardi Teknomo's Tutorial
Send your comments, questions and suggestions
This tutorial is copyrighted .
Preferable reference for this tutorial is
Teknomo, Kardi (2015). Finding Eigen Value of Symmetric matrix Using Microsoft Excel. http:\\people.revoledu.com\kardi\ tutorial\Excel\EigenValue.html