REPLACEBLOCK Function for Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_REPLACEBLOCK(array, i, j, replace_with)

Argument	Description	Example
array	The original array or range of values (generally larger than the block)	MUNIT(10)
i, j	The starting indices for the location of the block	1,4
replace_with	The array (block) containing the new values	{1,2;3,4}

Description

L_REPLACEBLOCK fills a niche (pun intended) where you need to replace either a single value or an entire block of elements within an array with a new value or array. When all other methods of modifying the array or matrix fall short, L_REPLACEBLOCK can get the job done.

Some matrix algorithms work with blocks, but Excel does not have an assignment operator such as A(i,j)=5 for easily replacing specific values in an array by location. You can use DROP, TAKE, CHOOSEROWS, CHOOSECOLS, and INDEX to retrieve a block from an existing array. But, if you want to replace a block based on the (i,j) location, it may be more tricky to use HSTACK, VSTACK to reassemble a new matrix. This is where L_REPLACEBLOCK may be handy.

Lambda Formula

This code for using L_REPLACEBLOCK 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)

/**
* Replace a block within an array by specifying the starting (i,j) location
*/
L_REPLACEBLOCK = LAMBDA(array,i,j,new_block,
LET(doc,"https://www.vertex42.com/lambda/replaceblock.html",
    mrows,ROWS(new_block),
    ncols,COLUMNS(new_block),
    MAKEARRAY(ROWS(array),COLUMNS(array),
        LAMBDA(r,c,
            IF(AND((r-i+1)>0,(r-i)<mrows,(c-j+1)>0,(c-j)<ncols),
                INDEX(new_block,r-i+1,c-j+1),
                INDEX(array,r,c)
            )
        )
    )
));

Named Function for Google Sheets

Name: L_REPLACEBLOCK
Description: Replace a block within an array by specifying the starting (i,j) location
Arguments: array, i, j, new_block
Function:

=LET(doc,"https://www.vertex42.com/lambda/replaceblock.html",
    mrows,ROWS(new_block),
    ncols,COLUMNS(new_block),
    MAKEARRAY(ROWS(array),COLUMNS(array),
        LAMBDA(r,c,
            IF(AND((r-i+1)>0,(r-i)<mrows,(c-j+1)>0,(c-j)<ncols),
                INDEX(new_block,r-i+1,c-j+1),
                INDEX(array,r,c)
            )
        )
    )
)

L_REPLACEBLOCK Examples

Example 1

Replace the upper left 2x2 block of a matrix with the identity matrix MUNIT(2). L_PASCAL is not necessary, but it's a convenient function for generating a sample matrix.

Test: Copy and Paste this LET function into a cell
=LET(
    array, L_PASCAL(4),
    i, 1,
    j, 1,
    L_REPLACEBLOCK(array,i,j,MUNIT(2))
)

Result:
{1,  0,  1,  1;
 0,  1,  3,  4;
 1,  3,  6, 10;
 1,  4, 10, 20}

Example 2

Replace a single value located at (3,2) with a blank "". This example is based on the "Vlookie the Gold Miner" case from the 2023 Microsoft Excel World Championship qualifying rounds. This example and function are not necessary for completing this case, but it's fun example of "mining" a particular element from the mine array.

Test: Copy and Paste this LET function into a cell
=LET(
    array, {"🪨","🩸","🌡️";"🧱","🔋","🩸";"🔩","🧱","🔋";"🪨","🧲","🧱"},
    i, 3,
    j, 2,
    L_REPLACEBLOCK(array,i,j,"")
)

Result:
{"🪨","🩸","🌡️";
 "🧱","🔋","🩸";
 "🔩", "" ,"🔋";
 "🪨","🧲","🧱"}

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.

L_REPLACEBLOCK

Description

Lambda Formula

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

Named Function for Google Sheets

L_REPLACEBLOCK Examples

Follow Us On ...