Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Sep 08, 2021
Solved

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...
excel
Formulas and Functions
office 365
  • tboulden's avatar
    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's avatar
tboulden
Iron Contributor
Sep 08, 2021

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's avatar
    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's avatar
      tboulden
      Iron Contributor
      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.

      • leolapa's avatar
        leolapa
        Brass Contributor
        Sep 08, 2021
        Thanks a lot!!!

Resources