Forum Discussion
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 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...
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.
9 Replies
- 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...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.
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)
- 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.- Can you try using some sample where it will occur?
