ONES and ZEROS

=ONES(array) :: Returns a matrix of 1s the size of array
=ONES(m,[n]) :: Returns a matrix of 1s the size of m x n (default n=1)
=ZEROS(array) :: Returns a matrix of 0s the size of array
=ZEROS(m,[n]) :: Returns a matrix of 0s the size of m x n (default n=1)

Download the Template

In the template file, navigate to the Matrices worksheet to see the ONES and ZEROS function in action.

Description

Although matrices of 1s and 0s are very easy to create with the SEQUENCE function, the ONES and ZEROS functions are included in the LAMBDA library because of how commonly they are used when working with matrices.

ONES(array)  =SEQUENCE( ROWS(array), COLUMNS(array), 1, 0 )
ZEROS(array) =SEQUENCE( ROWS(array), COLUMNS(array), 0, 0 )
ONES(n,m)  =SEQUENCE( n, m, 1, 0 )
ZEROS(n,m) =SEQUENCE( n, m, 0, 0 )

Lambda Function Code

This code for using ONES and ZEROS 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.

Name: ONES
Comment: Create a matrix of 1s that is the same size as the array or size m x n.
Refers To:

=LAMBDA(m_rows,[n_columns],
LET(doc,"https://www.vertex42.com/lambda/ones-and-zeros.html",
    rows,ROWS(m_rows),cols,COLUMNS(m_rows),
    IF(OR(rows>1,cols>1),
        SEQUENCE(rows,cols,1,0),
        IF(ISOMITTED(n_columns),
            SEQUENCE(m_rows,1,1,0),
            SEQUENCE(m_rows,n_columns,1,0)
        )
    )
))

Name: ZEROS
Comment: Create a matrix of 0s that is the same size as the array or size m x n.
Refers To:

=LAMBDA(m_rows,[n_columns],
LET(doc,"https://www.vertex42.com/lambda/ones-and-zeros.html",
    rows,ROWS(m_rows),cols,COLUMNS(m_rows),
    IF(OR(rows>1,cols>1),
        SEQUENCE(rows,cols,0,0),
        IF(ISOMITTED(n_columns),
            SEQUENCE(m_rows,1,0,0),
            SEQUENCE(m_rows,n_columns,0,0)
        )
    )
))

/**
* ONES(array) :: Returns a matrix of 1s the size of array
* ONES(m,[n]) :: Returns a matrix of 1s the size of m x n (default n=1)
*/
ONES = LAMBDA(m_rows,[n_columns],
LET(doc,"https://www.vertex42.com/lambda/ones-and-zeros.html",
    rows,ROWS(m_rows),cols,COLUMNS(m_rows),
    IF(OR(rows>1,cols>1),
        SEQUENCE(rows,cols,1,0),
        IF(ISOMITTED(n_columns),
            SEQUENCE(m_rows,1,1,0),
            SEQUENCE(m_rows,n_columns,1,0)
        )
    )
));
/**
* ZEROS(array) :: Returns a matrix of 0s the size of array
* ZEROS(m,[n]) :: Returns a matrix of 0s the size of m x n (default n=1)
*/
ZEROS = LAMBDA(m_rows,[n_columns],
LET(doc,"https://www.vertex42.com/lambda/ones-and-zeros.html",
    rows,ROWS(m_rows),cols,COLUMNS(m_rows),
    IF(OR(rows>1,cols>1),
        SEQUENCE(rows,cols,0,0),
        IF(ISOMITTED(n_columns),
            SEQUENCE(m_rows,1,0,0),
            SEQUENCE(m_rows,n_columns,0,0)
        )
    )
));

Name: ONES
Description: ONES(array,) or ONES(n,m) - Create a matrix of 1s that is the same size as the array or size m x n.
Arguments: param1, param2
Function:

LET(doc,"https://www.vertex42.com/lambda/ones-and-zeros.html",
    rows,ROWS(param1),cols,COLUMNS(param1),
    IF(OR(rows>1,cols>1),
        SEQUENCE(rows,cols,1,0),
        IF(ISBLANK(param2),
            SEQUENCE(param1,1,1,0),
            SEQUENCE(param1,param2,1,0)
        )
    )
)

Name: ZEROS
Description: ZEROS(array,) or ZEROS(n,m) - Create a matrix of 0s that is the same size as the array or size m x n.
Arguments: param1, param2
Function:

LET(doc,"https://www.vertex42.com/lambda/ones-and-zeros.html",
    rows,ROWS(param1),cols,COLUMNS(param1),
    IF(OR(rows>1,cols>1),
        SEQUENCE(rows,cols,0,0),
        IF(ISBLANK(param2),
            SEQUENCE(param1,1,0,0),
            SEQUENCE(param1,param2,0,0)
        )
    )
)

ONES and ZEROS Examples

Test: Copy and Paste this LET function into a cell
=LET(
    array, A1:C4,
    ZEROS(array)
)

Result: {0, 0, 0; 0, 0, 0; 0, 0, 0; 0, 0, 0}

=SEQUENCE(4,3,0,0)

Test: Copy and Paste this LET function into a cell
=LET(
    m_rows, 2,
    n_columns, 4,
    ONES(m_rows,n_columns)
)

Result: {1, 1, 1, 1; 1, 1, 1, 1}

=SEQUENCE(2,4,1,0)