Symmetric expressions in excel matrix

Question

I sometimes work with symmetric matrices in MS-Excel (both v2007 and v2003).

Is there an option to help me to copy expressions from the lower triangle to the upper one?

It should be something like copy and paste/transponse but those functions normally work only with rectangular areas.

in the added picture you can see an exemple of an expression that I have to replicate by linking the symmetric value in the superior triangle of the matrix.

Answer 1

To get the number in the appropriate cell, we can use OFFSET and the cell address the forms the base of the table. Note that the formula will produce a *Circular Reference` error if entered in on the diagonal. The formula will work for both sides of the diagonal - you just have to decide which one will hold the data, and which will hold the formula.

Offset takes Row and Column to decide the target. By subtracting the base cell row and column from the current position, we can invert the row and columns, and get the data.

Using your example, with the origin of the table in B2, we end up with the following formula:

=OFFSET($B$2,COLUMN()-COLUMN($B$2),ROW()-ROW($B$2))

you can copy this formula into the cells, and get the reflection. Now you have the number, you can do any calculation you require on the reflection. Using your example, this would make the formula:

=10-OFFSET($B$2,COLUMN()-COLUMN($B$2),ROW()-ROW($B$2))

Result:

Using INDEX to make it non volatile would change the formula slightly. First, we would need a reference to the entire table, not just the top cell. Second, we would need to add 1 to the row/column calculation, as it refers to the first cell as row/column 1, not an offset of 0 as the previous formula.

=INDEX($B$2:$K$11,COLUMN()-COLUMN($B$2)+1,ROW()-ROW($B$2)+1)

and your example of 10-Cell would become:

=10-INDEX($B$2:$K$11,COLUMN()-COLUMN($B$2)+1,ROW()-ROW($B$2)+1)

Answer 2

As one of the above answers demonstrates, this can be done by using Excel formulas. I however find this to be a very tedious procedure. Especially if this is something you need to do on a regular basis. In that case VBA could save you a lot of time.

The following code will work on a square selection and fill the rest of the matrix no matter if it is the lower- or upper part of the matrix that is pre-filled.

Option Explicit

Sub FillSymetricMatrix()
    Dim i As Integer, j As Integer
    Dim SelRng As Range
    Dim FillArea As String
    Dim FRow As Integer
    Dim FCol As Integer

    Set SelRng = Selection
    FRow = SelRng.Rows(1).Row
    FCol = SelRng.Columns(1).Column

    'Returns information about which area to fill
    If ActiveSheet.Cells(FRow + SelRng.Rows.Count - 1, FCol).Value <> vbNullString Then       'Lower filled
        If ActiveSheet.Cells(FRow, FCol + SelRng.Columns.Count - 1).Value = vbNullString Then 'Upper empty
            FillArea = "Upper"
        Else
            FillArea = "Error"
        End If
    Else
        If ActiveSheet.Cells(FRow, FCol + SelRng.Columns.Count - 1).Value <> vbNullString Then 'Upper filled
            FillArea = "Lower"
        Else
            FillArea = "Error"
        End If

    End If

    'Determines if the selection is square
    If SelRng.Rows.Count <> SelRng.Columns.Count Then FillArea = "Error"


    'Fills empty area of the square (symetric) matrix
    Select Case FillArea
        Case Is = "Upper"
            For i = 0 To SelRng.Rows.Count - 1 Step 1
                For j = 0 To SelRng.Columns.Count - 1 Step 1
                    If i <= j Then ActiveSheet.Cells(i + FRow, j + FCol).Value = ActiveSheet.Cells(j + FRow, i + FCol).Value
                Next j
            Next i

        Case Is = "Lower"
            For i = 0 To SelRng.Rows.Count - 1 Step 1
                For j = 0 To SelRng.Columns.Count - 1 Step 1
                    If i <= j Then ActiveSheet.Cells(j + FRow, i + FCol).Value = ActiveSheet.Cells(i + FRow, j + FCol).Value
                Next j
            Next i

        Case Else
            MsgBox "The procedure cannot be performed on the current selection!"
    End Select
End Sub