+ Reply to Thread
Results 1 to 3 of 3

How to make a symmetric matrix from asymmetric matrix by averaging two values

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    Michigan
    MS-Off Ver
    2011
    Posts
    4

    How to make a symmetric matrix from asymmetric matrix by averaging two values

    Hello experts,

    I have an asymmetric matrix like below (this is an example and the actual one has 50 columns and 50 lows).

    A B C D
    1 100 20 10 5
    2 24 100 60 2
    3 20 50 100 4
    4 1 2 6 100

    From the matrix above, I would like to get an average of two values in diagonal direction (e.g. A2&B1, A3&C1) and make a symmetric matrix like below. Is there any way to do it for many rows and columns? Any advice or help would be greatly appreciated.

    A B C D
    1 100 22 15 3
    2 22 100 55 3
    3 15 55 100 5
    4 3 3 5 100

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: How to make a symmetric matrix from asymmetric matrix by averaging two values

    Try this.

    Please Login or Register  to view this content.
    Open up the VBA editor by hitting ALT F11

    Insert a new module by hitting Insert - Module

    Paste the macro into the empty sheet

    Hit ALT F11 to get back to the worksheet.

    Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.
    Martin

  3. #3
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: How to make a symmetric matrix from asymmetric matrix by averaging two values

    Hi,

    Assuming your data above is located at Sheet1 ! A1:D4, then the formula at Sheet2 ! A1 is :

    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

+ 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. Eigenvector of matrix for nxn matrix of variable size
    By rocketscientist165 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2014, 10:51 AM
  2. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  3. [SOLVED] Removing duplicate headers and creating symmetric matrix
    By guest2013 in forum Excel General
    Replies: 4
    Last Post: 08-19-2013, 12:54 AM
  4. Formulas for sum two symmetric values in a quadratic matrix
    By davide88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2013, 12:38 AM
  5. 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

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