Forum Discussion

leolapa's avatar
leolapa
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...
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.

Juliano-Petrukio's avatar
Juliano-Petrukio
Bronze Contributor
Sep 08, 2021

leolapa 

Consider using IFERROR formula

 

=IFERROR(YourFormula,PreviousCellValue)

=IFERROR(INDEX($B$2:$D$11;$AM$2:$AO$11;SEQUENCE(;COLUMNS($B$1:$D$1)));C2)

 

=IFERROR(YourFormula,C2)

  • leolapa's avatar
    leolapa
    Brass Contributor
    Sep 08, 2021
    That solution would indeed work just fine in case it was 100% guaranteed that STOCKHISTORY would return only one consecutive date with #N/A.
    However in real world there might be occasions where #N/A may show up in two or more consecutive dates, and in those cases that solution would still return #N/A since it doesn't look up for the valid price from the last available date.
    • Juliano-Petrukio's avatar
      Juliano-Petrukio
      Bronze Contributor
      Sep 08, 2021
      Can you try using some sample where it will occur?
      • leolapa's avatar
        leolapa
        Brass Contributor
        Sep 08, 2021
        The thing is that your proposed approach contains one "static" cell within it (in that case, C2), so my understanding is that requires it to be dragged along down and across whatever range size I'm dealing with, so since it's not a one-cell "spilled" type solution it won't work for me, as my goal is to provide a solution that will deal with a growing table (both in columns - that is dates, and rows - that is stock tickers).

Resources