+ Reply to Thread
Results 1 to 6 of 6

Eigenvector of matrix for nxn matrix of variable size

  1. #1
    Registered User
    Join Date
    05-04-2014
    Location
    Hamburg, Germany
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    3

    Eigenvector of matrix for nxn matrix of variable size

    Hello!
    I am new here and apologize in advance for any silly question I might ask. I've not had any experience with vba until now.
    I am trying to create an easy to use interface for R&D project selection with a pairwise comparison of criteria (see AHP). So far I was able to generate a userform that lets the user select and deselect existing criteria and add new ones. these n criteria are then inserted in the appropriate cells in the worksheet and the n x n weighting matrix is created. Then I created a userform with a slidebar that lets the user compare each criterion with all others and inserts the values in the lower triangular matrix. the values of the upper triangular matrix are generated using =MTRANS. So far this works, sort of.

    My problem now is that since the n criteria can be selected the matrix size can change (n x n).

    To check for consistency of the weighting input I used a 5 x 5 matrix and manually determined the eigenvalue by squaring and normalizing the matrix etc. and hence getting the priority vector.

    So my questions are:

    1. How do I compute the eigenvector of a matrix with VBA (or simpler: how do I square matrices, sum rows or colums, normalize and determine row averages)?
    2. How do I do this with a variable n x n matrix?

    Thank you in advance!

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Eigenvector of matrix for nxn matrix of variable size

    First, let me just say that Excel may not be the right tool for you. Excel has some matrix functions and VBA can be used to do quite a bit, but if you are dealing with a lot of matrices, then go get some software specifically designed for that type of analysis (Matlab, Mathcad, Mathematica). It will just make things easier and you don't have to worry about learning VBA or hoping your code doesn't have some bugs in it. What you are asking for is fairly complex, and the fact that you are unsure how to handle dynamic matrix sizes (which is fairly straight forward), implies you are going to be working on this for some time.
    Alternatively, you could try Wolfram Alpha's website. It offers quite a bit for free. Here is a link to their matrix examples:
    https://www.wolframalpha.com/examples/Matrices.html

    Finally, if you wish to learn coding, as I doubt many people on this site are going to volunteer to create a dynamic matrix calculator that meets all of your criteria, I would recommend John Walkenbach's Power Programming in VBA book (or there is plenty of guidance on the web) and also the "Numerical Recipes in ...." book by William H Press (and team). There are a few in this series based upon the language (FORTRAN, C, C++), but they all pretty much have the same algorithms. I'd recommend the one that includes source code on a CD in order to remove the possibility of typos. I know the Numerical Recipes books have quite a bit on matrix math.

    Some low hanging fruit:
    square a matrix - Use MMULT
    summing rows/cols - Use SUM (or use a table with a totals row and a totals column you create using SUM)
    normalize - the matrix or its columns (e.g. vectors)? Assuming you are normalizing the columns, then it is each value divided by the sqrt of sumproduct of each column.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    05-04-2014
    Location
    Hamburg, Germany
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    3

    Re: Eigenvector of matrix for nxn matrix of variable size

    Hello Pauley,
    thank you for your quick reply. I haven't considered using Matlab. I will look into it, although I never really got the hang of it. And thanks for the book info.
    I used the mmult functions in excel, but my problem was that this doesn't work if there are empty cells in the matrix, i.e. if I change my matrix from 5x5 to 3x3 or so. that's why I thought to do it through vba saying something like "if cells within range(...) are filled, then multiply the matrix (of size nxn) with itself and put the result in range(...)"
    I might leave out the option to change the number of criteria. It's not vital for my project and apparently this is a bigger issue (for me) than I thought.
    Thank you though for trying!
    Lena

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Eigenvector of matrix for nxn matrix of variable size

    To handle the different sizes, you could create a dynamic named range. In Name Manager create a name (e.g. MatrixIn) and then use a formula like this to define the matrix:
    OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A$1:$BA$1),COUNTA(Sheet2!$A$1:$A$53))
    Where Sheet2!$A$1 is the top left corner of my matrix, and I limited the size of this one to be a max of 53x53.

    Then, in another area, probably another sheet so as to avoid cell conflicts, highlight a 53x53 block of cells and type
    =MMULT(MatrixIn,MatrixIn)
    and then use ctrl-shift-Enter to have this entered as an array formula.

    You will see your MMULT result an then a bunch of N/A errors in the columns and rows that are beyond your base matrix size. You can use some conditional formatting to hide those, but I do know that using IFERROR did not work for me.

  5. #5
    Registered User
    Join Date
    05-04-2014
    Location
    Hamburg, Germany
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    3

    Re: Eigenvector of matrix for nxn matrix of variable size

    hey,
    thank you again but could you explain what the two zeros stand for?

  6. #6
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Eigenvector of matrix for nxn matrix of variable size

    The two zeroes in the OFFSET function? They simply state the x and y offset from the base address (A1 in this case). Excel Help can provide more detail.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  2. Using a transition matrix as input to a cummulative matrix
    By Walter12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2012, 04:19 AM
  3. Copy Data Macro - Autoadjust matrix size?
    By heatwave in forum Excel General
    Replies: 3
    Last Post: 03-23-2011, 06:58 AM
  4. Variable Matrix Size
    By SpeedoJimmy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2009, 05:36 AM
  5. [SOLVED] How to import a matrix of size 2057x2057
    By Kiran in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-28-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1