In the world of matrix problems, Eigenvalue and Eigenvector are one of the most important terms. They are widely used to solve linear equations reducing dimension space. In this article, let us learn how to calculate eigenvalues and eigenvectors in Excel with 2 useful methods.
Download Practice Workbook
Get this practice file and try the methods by yourself.
What Are Eigenvalues?
The Eigenvalues are a set of scalar values associated with a set of linear equations that has a non-zero absolute solution. People also call it Characteristic Roots. The process results in a set of Determinants that has this equation:
Here, the scalar Lambda (λ) is the required eigenvalue that is associated with square k x k matrix A and Identity matrix I.
What Are Eigenvectors?
In a linear equation, along with calculating Eigenvalues, we also calculate the Eigenvector of a matrix. It is a sort of vector that is generated through the scalar values. It is also called proper vectors, characteristic vectors, or latent vectors. We can symbolize it with an X in the Determinant equation which results in this:
2 Useful Methods to Calculate Eigenvalues and Eigenvectors in Excel
So far, we learned about eigenvalues and eigenvectors in brief. Now let us calculate them with the following 3X3 Matrix A.
1. Calculate Eigenvalues and Eigenvectors with Goal Seek in Excel
In this first method, we will process the calculation with the Goal Seek feature in Excel. Let’s see how it works.
- First, insert a general Identity Matrix in the Cell range F5:H7 where we have 1 in the diagonal cells.
- Second, create a new column to find the Determinant where the initial scalar Lambda (λ) is 0.
- Then, insert this formula in Cell B11 to find the 1st value of the k x k matrix based on the determinant equation.
- Similarly, apply the same formula based on each cell of the A matrix.
- Therefore, the output looks like this.
- Now, insert this formula into Cell C15 to find the Determinant value.
- Next, go to the Data tab and select Goal Seek from the What-If Analysis option.
- Then, refer Determinant value as the Set cell and Lambda (λ) value as By changing cell.
- Along with this, determine the To value as 0 as we want the Determinant value equal to zero.
- Following this, press OK and after some time you will get the new Eigenvalue along with the Determinant value in Cells C10 and C15 respectively.
- Next, change the Lambda (λ) value to 1.5 and run the same process in Goal Seek to get another set of values as follows.
- Similarly, insert 3 as Lambda (λ) value and run the Goal Seek operation to get the following output.
- So far, we got 3 sets of eigenvalues as the Lambda (λ) value.
- Lastly, insert this formula for each set of eigenvalues and get the final eigenvectors like this.
2. Apply Power Method to Get Eigenvalues and Eigenvectors
We can apply the Power Method in Excel to find the eigenvalues and eigenvectors along with the dominant one. To do this, follow the simple steps below.
- In the beginning, create an initial vector column with the following values in the Cell range F5:F7.
- Then, insert the values as a row vector in the Cell range C10:E10 for ease of the process.
- Next, insert this formula in Cell F10 to transpose and return the absolute vector value.
- Afterward, press Enter and you will get the following output where k=0.
- Next, insert this formula in Cell I10 to find the Maximum Absolute Eigenvalue.
- Next, select the Cell range C11:E11 and insert this formula to get the next horizontal vector value where k=1.
- Following this, select the Cell range F10:I10 and drag its bottom corner downward as shown below.
- After this, insert this formula in Cell J11 to get the absolute value of Epsilon (e) in the power method.
- Lastly, select the Cell range B11:J11 and drag the bottom corner of the last cell downward until it shows repetitive values like this.
- So far, we got the eigenvalues (mk+1) and eigenvectors (V[k]T) for each value of k.
- Therefore, analyzing the dataset above, delete the rows that have repetitive values.
- Accordingly, comparing the last value of Epsilon (e) which is 0.0001 with the standard value 0.0005 we can get that 0.0001 < 0.0005.
- As a result, we have got our dominant eigenvalue and eigenvectors based on the adjacent cell values as shown below.
Finally, we are at the end of our article. Here we tried to explain how to calculate eigenvalues and eigenvectors in Excel with 2 useful methods. Let us know your feedback in the comment box. Follow ExcelDemy for more tutorials.