4

So, I'm working on a project in Google Sheets that has me creating a symmetric matrix. I've filled one half of it with the correct values, but I'm not happy with the idea that I have to reinput the same data (or cell references) by hand on the other half.

Is there either an option that will do this for me, or a way to otherwise automate the process? I know about paste transpose, but that gives me the same chart, with the empty half below rather than above, and still no way to get the data into the other half. I suppose I'd also accept a way to combine the two sheets, putting the filled values from one into the empty values from the other, or some sort of "transparent" paste where an empty cell in the pasted range doesn't overwrite the data in the cell it's pasted over.

Screenshot of what is filled in so far:

screenshot

  • 2
    Please create a simple version of the spreadsheet and share it with the community. Also, please clearly identify which portion is the input and which portion should be the output. – daniel Feb 19 '16 at 7:04
4

The following formula refers to the transpose of the current cell:

=indirect("R"&column()&"C"&row(), False)

Indeed, it takes the row and column numbers of the current cell and swaps them to get a cell reference in R1C1 format.

However, having to paste this in every row of upper-half of the matrix separately is not appealing. (One can't select a triangular region to put the formula in). So here is a better way:

Suppose your current matrix is in the sheet named "matrix". In cell A1 of another sheet, enter

=if(isblank(matrix!A1), indirect("matrix!R"&column()&"C"&row(), False), matrix!A1)

and extend this formula to a square region the size of the matrix. You get a symmetric matrix in that sheet: the formula takes either the matching cell in sheet "matrix" or its transpose, whichever is nonempty. Then select the matrix and copy values only (Ctrl-Shift-V) back into the original sheet.


Alternatively (if you don't want to mess with Indirect, maybe because your matrix is off-center and you'd need some adjustments to row/column numbers), use three sheets: one for lower half matrix, one for its transpose, and one more to combine them:

 =if(isblank(matrix!A1), transpose!A1, matrix!A1)
  • Aha! For my purposes, both of those options worked perfectly. In particular, the indirect option was helpful since I needed to create three versions of the sheet with minor differences. I made three copies with no formulas, used the "matrix" copy to make the changes, and then was able to copy the values from the transposed sheet to a fresh copy of the original. Thanks! – Nicholas McIntyre Feb 19 '16 at 18:35
0

Given (input values/formulas)

A lower half of a quasi-simetric matrix plus an exception of the upper half of the quasi-simetric matrix

-           
a   -   g   
b   c   -   
d   e   f   -

Then (expected result)

The quasi-simetric matrix

-   a   b   d
a   -   g   e
b   c   -   f
d   e   f   -

The following formula returns the desired result

=ArrayFormula(IF(LEN(A1:D4),A1:D4,transpose(A1:D4)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.