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}\$
Finally for l =3, the algorithm converges towards l = 3,41421511.
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.
\$\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
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.
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}\$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}\$.
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