Get the maximum of the positions that are symmetric diagonally

Question

I have a matrix like list1.I want to get the maximum at the two positions that are symmetric diagonally, and get the new matrix(list2).How can I run R or Excel? list1

    A   B   C   D
A   11  9   11  11
B   4   3   4   4
C   14  8   15  12
D   9   6   9   8

list2

    A   B   C   D
A   11  9   14  11
B   9   3   8   6
C   14  8   15  12
D   11  6   12  8

Answer 1

In Excel, the following array formula does the same job:

=IF(A1:D4>TRANSPOSE(A1:D4),A1:D4,TRANSPOSE(A1:D4))

must be entered using CtrlShiftEnter in older versions of Excel.

In Excel 2019 I had to select G1 to J4 before entering the formula. In O365, this is automatic using the new spill formulas.

Works for arrays as well as ranges.

Answer 2

In R, if you have the matrix

mm <- matrix(
  c(11L, 4L, 14L, 9L, 9L, 3L, 8L, 6L, 11L, 4L, 15L, 9L, 11L, 4L, 12L, 8L), 
  nrow=4, dimnames = list(c("A", "B", "C", "D"), c("A", "B", "C", "D"))
)

Then you can write a function to compare the symmetrix positions

max_sym_pos <- function(m) {
  vupper <- m[upper.tri(m)]
  vlower <- m[lower.tri(m)]
  vmax <- pmax(vupper, vlower)
  m[upper.tri(m)] <- vmax
  m[lower.tri(m)] <- vmax
  m
}
max_sym_pos(mm)
#    A  B  C  D
# A 11  9 14 11
# B  9  3  9  6
# C 14 11 15 12
# D  9  6 12  8

Answer 3

You can take the parallel max of the matrix and the transposed matrix:

pmax(M, t(M))

     [,1] [,2] [,3] [,4]
[1,]   11    9   14   11
[2,]    9    3    8    6
[3,]   14    8   15   12
[4,]   11    6   12    8