TRIANGLE Function in Excel

=TRIANGLE(array,[L_or_U],[k],[replace_with])

Argument	Description	Example
array	The given array or matrix	{1,2,3;4,5,6;7,8,9}
[L_or_U]	Default="U". "L" for lower triangle, "U" for upper triangle.	"L" or "U"
[k]	Default=0. Diagonal offset k=0 main diagonal, k>0 above main, k<0 below main	0
[replace_with]	Default=0. Off-triangle elements are replaced with this value.	NA()

Download the Template

In the template file, navigate to the Matrices worksheet to see the TRIANGLE function in action.

Description

The TRIANGLE function returns the upper or lower triangular portion of a 2-D array or matrix, based on a specified diagonal, k (default = 0). Elements outside the selected triangle are replaced with a constant (default = 0).

This is similar to MATLAB's tril and triu functions.

Lambda Formula

This code for using TRIANGLE in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Return the upper (U) or lower (L) triangle of a matrix.
* The parameter k specifies which diagonal to use, and off-triangle
* elements are replaced with a specified value.
*/
/*
* Inputs
*   array        : The given array or matrix.
*   [L_or_U]     : Default="U". "L" for lower triangle, "U" for upper triangle.
*   [k]          : Diagonal offset (like MATLAB's tril or triu).
*                  k = 0  : main diagonal (default)
*                  k > 0  : diagonal above the main
*                  k < 0  : diagonal below the main
*   [replace_with]: Default=0. Off-triangle elements are replaced with this value.
* Output
*   Returns an array of the same size as given array where only the selected
*   triangular part (based on L_or_U and k) is preserved; all other elements
*   are replaced with replace_with.
*/
TRIANGLE = LAMBDA(array,[L_or_U],[k],[replace_with],
LET(doc,"https://www.vertex42.com/lambda/triangle.html",
    version,"1.0.0 12/6/2025 - Original",
    L_or_U,IF(ISOMITTED(L_or_U),"U",L_or_U),
    k,IF(ISOMITTED(k),0,k),
    replace_with,IF(ISOMITTED(replace_with),0,replace_with),
    m,ROWS(array),
    n,COLUMNS(array),
    iMat,SEQUENCE(1,SEQUENCE(1,n),SEQUENCE(m)),
    jMat,SEQUENCE(SEQUENCE(m),1,SEQUENCE(1,n)),
    res,IF( L_or_U = "L",
        MAP(array,iMat,jMat,LAMBDA(cell,i,j,IF(j>i+k,replace_with,cell) )),
        MAP(array,iMat,jMat,LAMBDA(cell,i,j,IF(j<i+k,replace_with,cell) ))
    ),
    res
));

TRIANGLE Examples

Example 1 - Upper Triangle

Extract the lower triangle of a 3×3 matrix, replacing off-triangle elements with 0. This uses all the defaults.

=LET(
    matrix,
     {1,2,3;
      4,5,6;
      7,8,9},
    TRIANGLE(matrix)
)

Result: {1,2,3;
         0,5,6;
         0,0,9}

Example 2 - Lower Triangle with NA()

Extract the upper triangle starting at the diagonal above the main (k=1), replacing off-triangle values with NA(). You can use IFERROR to replace NA() with something else, like IFERROR(result,0).

=LET(
    matrix,
      {1,2,3;
       4,5,6;
       7,8,9},
    TRIANGLE(matrix, "U", 1, NA())
)

Result: {#N/A,    2,   3;
         #N/A, #N/A,   6;
         #N/A, #N/A, #N/A}

TRIANGLE

Description

Lambda Formula

Code for AFE Workbook Module (Excel Labs Add-in)

TRIANGLE Examples

See Also

Follow Us On ...