Forum Discussion

Brass Contributor

Sep 08, 2021

Replace #N/A with prior available values on a 2D running array with ONE "spill" formula

Below is a small sample from close market prices for 3 stock tickers using the STOCKHISTORY function: I realized that when no data is available for a particular stock, it then returns the #N/A...

2D Spilled Range - Replace NA With Last Value for Each ROW.xlsx23 KB

Excel

Formulas and Functions

office 365

tboulden

Sep 08, 2021

leolapa Ahh, sorry, misunderstood. This solution handles that case, however if the very first entry is an error, it will show the errors; only if there is a prior non-error value in the column will it show the value.

=LET(
    rng,$B$2:$D$11,
    rows_,SEQUENCE(ROWS(rng)),
    nonerror_rows,--(NOT(ISERROR(rng)))*rows_,
    working_rows,
        MAKEARRAY(
            ROWS(nonerror_rows),
            COLUMNS(nonerror_rows),
            LAMBDA(i,j,
                MAX(
                    1,
                    IF(
                        i=1,INDEX(nonerror_rows,i,j),
                        MAX(
                            INDEX(nonerror_rows,SEQUENCE(i),j)
                        )
                    )
                )
            )
        ),
    INDEX(rng,working_rows,SEQUENCE(,COLUMNS(rng)))
)

This is very similar to my answer on your prior post for last matching row.

tboulden

Iron Contributor

leolapaGive the below a try, leverages MAKEARRAY as in previous solution.

=LET(
    rng,B2:D11,
    MAKEARRAY(
        ROWS(rng),
        COLUMNS(rng),
        LAMBDA(i,j,
            IF(
                i=1,INDEX(rng,i,j),
                IFERROR(INDEX(rng,i,j),INDEX(rng,i-1,j))
            )
        )
    )
)

leolapa

Brass Contributor

Sep 08, 2021

I tried to apply that solution just as you did, but the thing is that it only addresses cases with only one instance of #N/A, since it just pulls the value from one row above.
Since there might be instances where STOCKHISTORY will return two or more consecutive dates with #N/A, how can I adapt that solution so it will look for the price from the last available date? A recursive LAMBDA approach? And if so, how to setup the exit path so the formula will return no error? Still wrapping my head around that...

tboulden

Iron Contributor

Sep 08, 2021

=LET(
    rng,$B$2:$D$11,
    rows_,SEQUENCE(ROWS(rng)),
    nonerror_rows,--(NOT(ISERROR(rng)))*rows_,
    working_rows,
        MAKEARRAY(
            ROWS(nonerror_rows),
            COLUMNS(nonerror_rows),
            LAMBDA(i,j,
                MAX(
                    1,
                    IF(
                        i=1,INDEX(nonerror_rows,i,j),
                        MAX(
                            INDEX(nonerror_rows,SEQUENCE(i),j)
                        )
                    )
                )
            )
        ),
    INDEX(rng,working_rows,SEQUENCE(,COLUMNS(rng)))
)

This is very similar to my answer on your prior post for last matching row.

leolapa
Brass Contributor
Sep 08, 2021
Thanks a lot!!!

Forum Discussion

Replace #N/A with prior available values on a 2D running array with ONE "spill" formula

Share

Resources