excelfunctionmatrixexcel-formula

Excel Matrix - Formula or Function to Determine Upper and Lower Triangular Sums


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. Matrix


Solution

  • 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))
    

    enter image description here


    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)
    

    enter image description here