I am trying to write an Excel formula(s) that would replicate the upper.tri, lower.tri and diag matrix functions in R.
In Excel the upper (right) sum - the green area in this example would be where the column number is greater than the row number and vice-versa for the orange area. The yellow cells (the diagonal) are where the row number is equal to the column number.
I can't find any built-in matrix function in Excel for this calculation though given there are ROW(), COLUMN() and SUMIF() functions I am sure there must be some elegant way to do it with a combination of these without writing VBA code. So the question basically is how do I determine the sum of a range of cells based on the values of their row and column references?
Please note. I do not want to accomplish this via conditional formatting or applying filters by color. This small example is just for illustrative purposes.
The desired sums are 71.85% upper, 20.6 lower, 7.55 diagonal.
For the Upper.tri:
=LET(_rg,D1:H5,SUM((SEQUENCE(ROWS(_rg))<SEQUENCE(,COLUMNS(_rg)))*_rg))
diagonal
=LET(_rg,D1:H5,SUM((SEQUENCE(ROWS(_rg))=SEQUENCE(,COLUMNS(_rg)))*_rg))
Lower
=LET(_rg,D1:H5,SUM((SEQUENCE(ROWS(_rg))>SEQUENCE(,COLUMNS(_rg)))*_rg))
Just for fun, older versions:
=SUMPRODUCT((ROW(A1:INDEX(A:A,ROWS(D1:H5)))<COLUMN(A1:INDEX(1:1,COLUMNS(D1:H5))))*D1:H5)
=SUMPRODUCT((ROW(A1:INDEX(A:A,ROWS(D1:H5)))=COLUMN(A1:INDEX(1:1,COLUMNS(D1:H5))))*D1:H5)
=SUMPRODUCT((ROW(A1:INDEX(A:A,ROWS(D1:H5)))>COLUMN(A1:INDEX(1:1,COLUMNS(D1:H5))))*D1:H5)