Sep 07 2021 08:40 PM - edited Sep 07 2021 08:56 PM
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 error.
The problem for me is that it screws up my day-to-day % variance calculation, among other things, so I want to replace those #N/A's with the last available trading day's price, like the screenshot below:
But here's the catch: only a single-cell "spill" formula will work for me. On the above screenshot, it needs to be a formula on cell "AQ2" that "spills" the values down and across, the same way the STOCKHISTORY function works.
Since I don't know a way to make the STOCKHISTORY function do that #N/A replacement job in and of itself, albeit it would be GREAT if it could, I had to find a workaround to make that happen for me.
So I did a couple of calculation steps in order to get the row numbers array that would serve as input to the final INDEX function, and managed to get to a 1D "spill" solution for each of the 3 individual tickers columns on that example, as shown below (I just don't know how to trick those 3 yellow-shaded validation arrays into one consolidated formula without returning error):
The couple areas highlighted in red indicate the rectified row numbers that will serve as replacements for the #N/A's that have prior values to be used on their places.
However the formula
=LET(Acumul_Range;$F$2#;Rows_Range_Seq;SEQUENCE(ROWS(Acumul_Range));Cols_Range;COLUMNS(Acumul_Range);--MID(BYCOL(1;LAMBDA(Agreg_Range;CONCAT(TEXT(MOD(SMALL((Rows_Range_Seq*100000)+(SEQUENCE(;Cols_Range)*(Acumul_Range=Agreg_Range));Rows_Range_Seq*Cols_Range);100000);"0000"))));(Rows_Range_Seq*4)-3;4))
only treats each column individually, and that's the piece that I'm missing to get to my final goal...
... which is the formula
=INDEX($B$2:$D$11;$AM$2:$AO$11;SEQUENCE(;COLUMNS($B$1:$D$1)))
that produces the final array previously shown:
If I can get that row number formula to work down and across a 2D range, then I would be able to replace that "$AM$2:$AO$11" piece of the INDEX function with the hash reference "$AM$2#" and therefore get this baby kicking and screaming across the finish line.
Any ideas/suggestions on how to finesse that intermediate formula?
Thanks in advance...
Sep 08 2021 01:56 AM - edited Sep 08 2021 04:17 AM
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)
Sep 08 2021 04:06 AM
@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))
)
)
)
)
Sep 08 2021 07:04 AM
Sep 08 2021 07:09 AM
Sep 08 2021 07:31 AM
Sep 08 2021 08:43 AM
Solution@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.
Sep 08 2021 09:23 AM
Sep 08 2021 09:27 AM
Sep 09 2021 02:39 PM
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))
Sep 08 2021 08:43 AM
Solution@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.