How to determine eigenvalues and eignevectors of a matrix in Excel

We would like to compute the eigenvalues and eigenvectors of the following matrix in Excel. BTW Excel does  not have any native function that can do that.

\$\begin{bmatrix} 2 & -1 & 0\\-1 & 2 & -1\\0 & -1 & 2\end{bmatrix}\$

The characteristic polynomial of this matrix is : \$det( A - \lambda I)\$ qui donne \$(2 - \lambda)(2 - \sqrt{2} - \lambda)(2 + \sqrt{2} - \lambda)\$ .

The analytical eigenvalues solutions are: \$\lambda_1=2 - \sqrt{2}=0.58578644\$, \$\lambda_2=2 \$ and \$\lambda_3=2 + \sqrt{2}=3.41421356\$

The eigenvectors are  :

\$\begin{Bmatrix} 1 \\ \sqrt{2}\\1\end{Bmatrix}\$, \$\begin{Bmatrix} 1 \\0\\-1\end{Bmatrix}\$ and  \$\begin{Bmatrix} 1 \\-\sqrt{2}\\1\end{Bmatrix}\$

Procedure

1st method

We start by creating two columns, one for  l (from  0 to 4) and the other for  \$ det( A - \lambda I)\$

Then we plot  \$det( A - \lambda I) = f(\lambda)\$

We can clearly see the three zeros of the plotted function.  Eventually we can decrease the step around each solution until the precision is satisfactory. 

2sd method

We start by providing the matrix A in Excel. Each element of the matrix is a different cell.

We store the value of l in a cell, and  \$ A - \lambda I\$ is calculate din a range then we compute  \$det( A - \lambda I) \$ using the function DETERMAT

we will then use a tool in Excel called Goal Seek.
Go the tab Data, click on What-If-Analysis then Goal Seek


We want to find the value of  l (By Changing cell) that make the determinant (Set cell ) equal to zero (To value)

When we click on the OK button, Excel find one of the three solutions.
For l =0, the algorithm used by Goal seek converges towards  l =0,585753691487601.


For  l =1.5, the algorithm converges towards l = 1,99999867564629. 


Finally for l =3, the algorithm converges towards l = 3,41421511.




Computing the eigenvectors 


The eigenvectors are only determined within an arbitrary multiplicative constant. Let the first element be 1 for all three eigenvectors. The eigenvector has the form  \$ {u}=\begin{Bmatrix} 1\\u_2\\u_3\end{Bmatrix} \$ and it is a solution of  the equation \$ A{u} = \lambda_i {u}\$ whare  \$\lambda_i\$ is one of the three eigenvalues.

The two remaining unknowns of the eigenvector are solution of the system:  \$ \begin{bmatrix} a_{22} - \lambda_i & a_{23} \\ a_{23} & a_{33} - \lambda_i \end{bmatrix} \begin{Bmatrix}u_2\\u_3\end{Bmatrix}=-\begin{Bmatrix} a_{12}\\a_{13}\end{Bmatrix}\$.

Therefore \$\begin{Bmatrix} u_2\\u_3\end{Bmatrix}=-\begin{bmatrix} a_{22} - \lambda_i & a_{23} \\ a_{23} & a_{33} - \lambda_i \end{bmatrix}^{-1}\begin{Bmatrix} a_{12}\\a_{13}\end{Bmatrix}\$

In Excel, the formula to type is =-MMULT(MINVERSE(B10:C11);A3:A4)


MMULT computes the product of two matrices


MINVERSE gives the inverse of a matrix.



The three calculated eigenvectors are: 




Commentaires

Posts les plus consultés de ce blog

Installation de glpi 0.9 sur un serveur Windows 2012 R2

Accéder à PhpMyAdmin sans mot de passe

Changement du fuseau horaire par GPO