+ Reply to Thread
Results 1 to 4 of 4

Inverse of Matrix 3x3 which all the elements are in the same row

  1. #1
    Registered User
    Join Date
    12-10-2019
    Location
    São Carlos, Brazil
    MS-Off Ver
    2013
    Posts
    2

    Inverse of Matrix 3x3 which all the elements are in the same row

    Hello!

    I need to calculate the inverse of a matrix where all the elements are in the same row. So if I have a 3x3 matrix, all the elements would be like Matrix.PNG
    So the first 3 elements in this row would represent the first row of matrix, elements 4-6 represent the second row and elements 7-9 represent the third row.
    The inverse needs to be calculated in one line as well.

    Thank you very much.

    PS: Copying the elements into different row to make it a standard square matrix is also not desirable, because the program will have thousands of matrixes (each one represents a subsequent second of the simulation), so each matrix must only take one line.

    PS2: I can only use the Minverse function in one interval of a square matrix, but not in a square matrix where the elements are all in the same row.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Inverse of Matrix 3x3 which all the elements are in the same row

    Restricted to single row operations (at least in the spreadsheet), I see two possible approaches to inverting a 3x3 matrix that is represented as a 1x9 vector:

    1) Pull out pencil and paper and work through the algebra so you know the formula for each "cell" of the matrix. Then enter the appropriate formula in each cell of you 1x9 output vector. WikiHow has an algebra walkthrough https://www.wikihow.com/Find-the-Inv...f-a-3x3-Matrix and, if you trust the result, this page has the final formulas https://www.dr-lex.be/random/matrix-inv.html The main difficulty with this approach is that the algebra is tedious, so you will need to pay careful attention to each step so that you don't make mistakes. Once you are confident in your algebra formulas, translating those to Excel should be straightforward.

    2) Move the inverse operation to another programming language (like VBA) and create a UDF that will take your 1x9 vector:
    2a) Rearrange from 1x9 to 3x3
    2b) Take the inverse using whatever matrix inverse function is available to that language (VBA can call Excel's MINVERSE() function, if you choose VBA).
    2c) Rearrange the resulting 3x3 inverse matrix back into a 1x9 vector and write that result back to the spreadsheet.

    Which approach do you want to take? What specific questions do you have about implementing that approach?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    12-10-2019
    Location
    São Carlos, Brazil
    MS-Off Ver
    2013
    Posts
    2

    Re: Inverse of Matrix 3x3 which all the elements are in the same row

    Hello, MrShortly!

    - The first suggestion you made would actually be simpler to me since I don`t program in VBA, but the matrix I have is actually 7x7 (I used a 3x3 example to simplify my inital post), so when I try to take its inverse, it gets me a new matrix in which each of the elements are way too large (more than a hundred multiplications per element).
    - I`ve been trying to create a new function using VBA, which takes 7 intervals (each interval being a row of the matrix), but since I have near zero knowledge in VBA, any suggestion would be welcome.

    Thanks again!

    PS:
    The matrix is of the following form:

    a11 0 a13 0 0 0 0
    a21 a22 a23 a24 0 0 0
    a31 a32 a33 a34 0 0 0
    a41 a42 a43 a44 0 0 0
    a51 0 0 0 a55 0 0
    0 0 0 0 0 a66 0
    a71 a72 0 0 0 0 a77

    I tried taking its inverse using the matrixcalc website
    and each element of the resulting matrix is really large as mentioned before
    Last edited by matheus.campanini; 12-10-2019 at 12:26 PM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Inverse of Matrix 3x3 which all the elements are in the same row

    I've put some thought into this, and I think the easiest way to do this while avoiding both the long and error prone algebra part (if it can even be solved algebraically for a 7x7 matrix) and avoiding VBA will be to use Excel's built in data table feature (https://www.excel-easy.com/examples/data-tables.html if you are unfamiliar). However, in the OP, you insist that you cannot rearrange the data into a 7x7 block to feed into the MINVERSE() function. In order for this approach to work, you are going to need to relax that restriction. As I noted in my response, we will need to convert the 1x49 vector into a 7x7 matrix, run the 7x7 matrix through the MINVERSE() function to get the resulting 7x7 inverse matrix. Finally, we can convert back to a 1x49 vector. We only need one 7x7 input matrix/block and one 7x7 output matrix block, and the data table feature will feed each row of data into that block and feed the output results back into the output row. But the matrices will need to be present in the sheet. If you agree to relaxing this restriction, here's how I proceeded:

    1) Assume main input is in columns A to AW starting in row 2 and continuing down.
    2) Assume main output is in columns BA to CW starting in row 2 and continuing down.
    3) I will do the matrix inversion in columns DA and on to the right.
    4) First I need a row index cell that will become the column input for the data table. I designate DA1 for this task and input 1 into DA1.
    5) I need a 7x7 block for the numbers 1 to 49 that will be column index inputs for an INDEX() function. I enter 1 to 49 in DB1 to DH7 (1 to 7 in DB1 to DH1, 8 to 14 in DB2 to DH2, and so on)
    6) In DI1 I enter my INDEX() formula that will be the main 7x7 input matrix =INDEX($A$2:$AX$1000,$DA$1,DB1) [note the mix of relative and absolute references for easy copying] Copy/paste/fill into DI1 to DO7.
    7) A simple MINVERSE() function will now give me the inverse of this block, so I array enter MINVERSE(DI1:DO7) into DP1:DV7.
    8) Now to set up the output data table. I need to take the 7x7 inverse block (DP1:DV7) and convert to a single row above where I want the data table output to be. I enter =DP1 into CA1 copy across to CG1, enter =DP2 into CH1 and copy across to CN1, =DP3 into CO1 and copy across to CU1, and so on
    9) I enter the numbers 1,2,3,4,5,... (fill down as far as needed) into BZ2:BZwhatever.
    10) Now create the data table by selecting BZ1:CXwhatever -> Data -> What If analysis -> Insert Data Table. Choose DA1 for the column input cell and, if you have set everything up right, then that block of cells should fill in with the appropriate values for each row.

    I have attached a small sample file showing how I set it up. If you can accept the matrix block of cells that performs the inversion, this should be expandable/adaptable to your spreadsheet.
    Attached Files Attached Files

+ 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. Find Matrix Inverse for 2*2 and 3*3 matrices
    By Nkrekor in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-11-2019, 01:15 AM
  2. Problem with inverse matrixes 6x6 matrix
    By fabio12 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-22-2014, 07:06 PM
  3. Calculate Matrix Inverse without the MINVERSE function in excel
    By shirleyxiao in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2013, 11:17 AM
  4. Need help with inverse matrix
    By kylwggns in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2012, 11:02 AM
  5. Inverse Matrix Function Problem
    By MichielVrijbergen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2011, 10:21 AM
  6. Inverse Matrix
    By adashiu in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-23-2010, 11:24 AM
  7. How to compute the inverse of a matrix with some complex elements
    By Annoushka42 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-05-2006, 08:10 PM

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