Below is a small sample from close market prices for 3 stock tickers using the STOCKHISTORY function:
Data.png
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:
Goal.png
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):
1D_Spill_Row_Num.png
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
only treats each column individually, and that's the piece that I'm missing to get to my final goal...
... which is the formula
that produces the final array previously shown:
2D_Spill_NA_Repl.png
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.
2D_Spill_NA_Repl_Complete.png
Any ideas/suggestions on how to finesse that intermediate formula?
Thanks in advance...
P.S.:
As usual I also post the same question under the Microsoft Tech Community forum, which is available on the link that follows:
Bookmarks