How to Calculate Eigenvectors in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

Eigenvector is related to linear algebra. When a set of equations forms a square matrix, then we will be able to form eigenvectors of that matrix. Eigenvector is very useful in differential equations and their associated applications. In this article, we will discuss how to form eigenvectors in Excel in detail.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What Are Eigenvectors?

A square matrix is a must requirement for Eigenvectors. It is a non-vector component that we get from the scalar multiplication of the given matrix. The equation related to the eigenvector is:

Av=位v

A= Given a matrix of size n x n

v=Eigenvector

=Eigenvalue

It is called the Eigenvector equation.

The eigenvector is also known as the characteristic vector, and the eigenvalue is known as the characteristic value.

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


Steps to Calculate Eigenvectors in Excel

The calculation of an eigenvector is a complex and time-consuming process mathematically. But here we need to do this in Excel. We will explain mathematical steps in this article and apply the vector formula of Excel to solve the mathematical steps. Look at the below section for details.


馃搶 Step 1: Insert Identity Matrix and Other Supporting Elements

First, we need to calculate the eigenvalues before going for eigenvectors. We already showed the mathematical expression eigenvector, that is,

Av=位v

or, Av-位v=0

or, v(A-位)=0

Finally, v(A-位I)=0

As the A is a matrix, we need another matrix with the scaler . So, we multiplied by the Identity matrix. The Identity matrix will not change the value of the equation. In the calculation of eigenvalues, the determinant, det(A-位I) considered being 0.

  • We will consider the following matrix.

Sample matrix for Eigenvectors

  • We insert an Identity Matrix, I of size 3脳3 in the dataset.
  • Also, added other supporting components to the dataset.

  • Now, find out A-位I by putting the formula on Cell F10.
=B5:D7-I4*B10:D12

Matrix subtraction for calculating Eigenvectors


馃搶 Step 2: Calculate the Determinant Using Excel MDETERM Function

Excel has a default function, which is named MDETERM,聽for calculating determinants.

  • Now, we will find out the det(A-位I) using the following formula on Cell H5.
=MDETERM(F10:H12)

Calculate determinant of matrix for Eigenvectors


馃搶 Step 3: Enable Excel Goal Seek Feature

Now, we will apply the Goal Seek feature to determine the eigenvalues.

  • Go to the Data聽tab,
  • What-If-Analysis option from the Forecast聽group.
  • Then, choose the Goal Seek聽option.

Enable goal seek feature in Excel


馃搶 Step 4: Calculate Eigenvalues

As the given matrix is 3脳3, we will get 3 eigenvalues. And for each eigenvalue, we will get one eigenvector.

  • We add new cells in the dataset for 3 eigenvalues and eigenvectors.

The Goal Seek window appears by following Step 3. We will insert cell references for two sections and set the determinant to zero (0).

  • Look at the following image to set the cell.
  • Finally, click on the OK聽button.

Calculate Eigenvalue for Eigenvectors

Here, the eigenvalue is the changing cell that we will get by using the Goal Seek feature.

  • After processing, we see the optimized eigenvalue.

This is the 1st eigenvalue.

  • Now, copy Cell I4 by pressing Ctrl+C.
  • We will paste this value on Cell L5 as 1. Go to the Values option of the Paste Special聽section.


馃搶 Step 5: Find Eigenvectors for Each of the Eigenvalues

We already calculated the 1st eigenvalue. Now, we will find out the corresponding eigenvector of the calculated eigenvector.

  • Now, copy matrix A-位I using the following equation on Cell B15.
=F10:H12

  • We need to copy the 1st and 2nd column of matrix A-位I on Cell E15 using the following formula.
=F10:G12

We get a 3脳5 matrix. This will be used in the calculation of the eigenvectors. Each eigenvector must be a column matrix. As the given matrix is of size 3脳3, the eigenvectors will be of size 3脳1.

  • We will use the MDETERM function to calculate the eigenvectors. Look at the formulas used on Cells G15:G17.

Formula in Cell G15:

=MDETERM(C15:D16)

In Cell G16:

=MDETERM(D15:E16)

In Cell G17:

=MDETERM(E15:F16)

Calculate Eigenvectors from eigenvalue

  • Now, optimize the eigenvector and set that on Cell L7 using the following formula.
=G15/MIN(ABS($G$15:$G$17))

Calculate Eigenvectors from eigenvalue

  • Now, use the paste-special technique to keep the values only.


馃搶 Step 6: Calculate Rest of the Eigenvalues and Eigenvectors in Excel

We will repeat Steps 4 and 5 again to get the rest of the eigenvalues and their corresponding eigenvectors.

  • Previously, set the value of as negative. Now, set this a positive value and apply the Goal Seek聽feature.

  • We get a new eigenvalue and eigenvector for that eigenvalue as shown before.
=G15/MIN(ABS($G$15:$G$17))

  • Now, set the eigenvalue to 0 and apply the Goal Seek feature again.

  • Finally, we get three eigenvalues and their corresponding eigenvectors.

Finally, we get V1, V2, and V3 are the 3 eigenvectors of three eigenvalues -6,15,3 respectively. And all eigenvectors are column vectors.

Read More: How to Calculate Eigenvalues and Eigenvectors in Excel


Conclusion

In this article, we described the eigenvalues and eigenvectors. We showed the calculation of eigenvalues and eigenvalues in Excel in detail. I hope this will satisfy your needs. Please give your feedback and suggestions in the comment box.


Related Articles

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo