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.

leolapa's avatar
leolapa
Brass Contributor
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).
SergeiBaklan's avatar
SergeiBaklan
MVP
Sep 09, 2021

leolapa 

Just for the interest recursion solution. Not very practical since max about 300 rows spill size.

FillDown = lambda(array, [m],
    LET( n, IF(ISBLANK(m), rows(array), m),
        IF( n = 1, INDEX(array, 1),
            LET( a, FillDown(array, n-1),
                IF( SEQUENCE(n) < n, a,
                    IFNA(INDEX(array, n ), INDEX( a, n-1) )
                )
            )
        )
    )
);

and

=FillDown(STOCKHISTORY($C$1,A2,A2+300,0,0,1))

 

Resources