DIAG Function in Excel

=DIAG(array)

Argument	Description	Example
array	Either a vector or a square array	{1,2;2,3}

Download the Template

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

Description

The DIAG function is used to convert a vector to a Diagonal matrix, or vice versa. A square Diagonal matrix is a matrix where all of the off-diagonal elements are zero.

If array is a column vector, the function returns a square diagonal matrix D with the vector making up the elements along the diagonal.

If array is a square matrix, the function returns a vector with elements from the diagonal of the array.

If array is not a column vector and not square, the function returns "Error: Not Square".

Multiplying by a Diagonal Matrix Scales

Multiplying an nxm array by an mxm Diagonal matrix will scale each of the columns of the original array by the corresponding diagonal elements. For example, if you multiply the ONES(3,3) matrix by DIAG({1;2;3}), you will end up with {1,2,3;1,2,3;1,2,3}. The first column is scaled by 1; the second column by 2; the third by 3.

=MMULT(ONES(3,3),DIAG({1;2;3}))
={1,2,3;1,2,3;1,2,3}

TRACE of a Matrix

The TRACE of a matrix is the sum of the diagonal elements, so...

TRACE(matrix) = SUM(DIAG(matrix))

Lambda Formula

This code for using DIAG 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 to Create Function via the Name Manager

Name: DIAG
Comment: Return the Diagonal matrix or vector for a given array
Refers To:

=LAMBDA(array,
LET(doc,"https://www.vertex42.com/lambda/diag.html",
    r, ROWS(array),
    c, COLUMNS(array),
    IF(c=1,
        MAKEARRAY(r,r,LAMBDA(i,j,IF(i=j,INDEX(array,i),0))),
        IF(r <> c, "Error: Not Square",
        MAKEARRAY(r,1,LAMBDA(i,j,INDEX(array,i,i)))
        )
    )
))

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

/**
* Convert a vector to a Diagonal matrix or vice versa
*/
DIAG = LAMBDA(array,
LET(doc,"https://www.vertex42.com/lambda/diag.html",
    r, ROWS(array),
    c, COLUMNS(array),
    IF(c=1,
        MAKEARRAY(r,r,LAMBDA(i,j,IF(i=j,INDEX(array,i),0))),
        IF(r <> c, "Error: Not Square",
        MAKEARRAY(r,1,LAMBDA(i,j,INDEX(array,i,i)))
        )
    )
));

Named Function for Google Sheets

Name: DIAG
Description: Return the Diagonal matrix or vector for a given array
Arguments: array
Function:

LET(doc,"https://www.vertex42.com/lambda/diag.html",
    r, ROWS(array),
    c, COLUMNS(array),
    IF(c=1,
        MAKEARRAY(r,r,LAMBDA(i,j,IF(i=j,INDEX(array,i),0))),
        IF(r <> c, "Error: Not Square",
        MAKEARRAY(r,1,LAMBDA(i,j,INDEX(array,i,i)))
        )
    )
)

DIAG Examples

Example 1

Create a Diagonal matrix from a given vector.

Test: Copy and Paste this LET function into a cell
=LET(
    vector, {1;2;3;4},
    DIAG(vector)
)

Result: {1,0,0,0;0,2,0,0;0,0,3,0;0,0,0,4}

Example 2

Form a vector from the given Diagonal matrix

Test: Copy and Paste this LET function into a cell
=LET(
    matrix, {1,0,0,0;0,2,0,0;0,0,3,0;0,0,0,4},
    DIAG(matrix)
)

Result: {1;2;3;4}

Example 3

Pull the diagonal elements out of a square matrix and

Test: Copy and Paste this LET function into a cell
=LET(
    matrix, {"A",1,2;1,"B",2;1,2,"C"},
    DIAG(matrix)
)

Result: {"A";"B";"C"}

Example 4

Calling the DIAG function twice for a matrix will return the matrix with all the off-diagonal elements changed to zero.

Test: Copy and Paste this LET function into a cell
=LET(
    matrix, {"A",1,2;1,"B",2;1,2,"C"},
    DIAG(DIAG(matrix))
)

Result: {"A",0,0;0,"B",0;0,0,"C"},

References & Resources

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.

DIAG