Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Eigenvalues and Eigenvectors in Excel

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:

(A – λI)=0

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:

(A – λI)X=0

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.

Eigenvalues and Eigenvectors in Excel


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.

Calculate Eigenvalues and Eigenvectors with Goal Seek in Excel

  • 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.
=B5-$C$10*F5

Calculate Eigenvalues and Eigenvectors with Goal Seek in Excel

  • 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.
=MDETERM(B11:D13)

Calculate Eigenvalues and Eigenvectors with Goal Seek in Excel

Here, the MDETERM function returns the value of the determinant from the B11:D13 array.
  • 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.
=-MMULT(MINVERSE(C12:D13),B6:B7)

Eigenvalues and Eigenvectors in Excel

In this formula, the MMULT function calculates the product of the k x k matrix both the initial and the new one. Along with it, the MINVERSE function provides the inverse of the matrix.

Read More: How to Calculate Vector Multiplication in Excel (2 Useful Examples)


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.

Apply Power Method to Get Eigenvalues and Eigenvectors

  • 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.
=TRANSPOSE(ROUND(MMULT($B$5:$D$7,TRANSPOSE(C10:E10)),4))

Apply Power Method to Get Eigenvalues and Eigenvectors

Here, the TRANSPOSE function converts any given vertical range to a horizontal range and vice-versa. Then, the ROUND function returns the output as a rounded number. Lastly, the MMULT function calculates the product of the matrix $B$5:$D$7, where we set it as an absolute cell reference. Lastly, we provide 4 in the num-digits argument to get up to 4 decimal places.
  • 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.
=MAX(ABS(F10:H10))

Apply Power Method to Get Eigenvalues and Eigenvectors

Here, the MAX function returns the maximum value from the Cell range F10:H10. Also, the ABS function returns an absolute value of this omitting negative values.

  • Next, select the Cell range C11:E11 and insert this formula to get the next horizontal vector value where k=1.

=F10:H10/I10

  • 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.

=ABS(I11-I10)

Apply Power Method to Get Eigenvalues and Eigenvectors

  • 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.

Read More: How to Apply Vector Formula in Excel (5 Suitable Examples)


Conclusion

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.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo