Dynamic interpolation/extrapolation

[Excelcomplicator]

Hello, I've come across a problem that has stumped me.

I am trying to populate a table of data using the following rules:
>if whole row is empty, keep whole row empty
> if cell is empty, use next nonblack cell
>if empty cell is between 2 nonblack cells, interpolate between the values
>if final cell in not populated, use previous non blank cell.

this process needs to be automated because depending on what drop down is selected. different cells in the table will be populated and empty.
the formula needs to identify these changes automatically.

is this something that can be done?

printscreen below shows table values in columns Y to AJ - these populated cells can change depending on what options are selected in column V (hense dynamic).
I am trying to populate cells in AL onwards using the rules mentioned above.

any help would be appreciated.

I am hoping for something formula based and not using VBA

 

[JamesCanale]

This got silly.

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
2	Thing	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023		4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023
3	A								382	254							382	382	382	382	382	382	382	382	254	254	254	254	254	254
4	B				451					588							451	451	451	451	478.4	505.8	533.2	560.6	588	588	588	588	588	588
5	C	232															232	232	232	232	232	232	232	232	232	232	232	232	232	232
6	D			529											175		529	529	529	496.8182	464.6364	432.4545	400.27273	368.09091	335.90909	303.7273	271.5455	239.3636	207.1818	175
Sheet22

Cell Formulas
Range	Formula
C2:O2	C2	=EOMONTH(B2,0)+1
Q2:AD2	Q2	=B2
Q3:Q6	Q3	=IF(B3<>"",B3,IF(COUNT($B3:$O3)=0,"",IF(COUNT(A3:$B3)=0,INDEX($B3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX($B3:$O3,1,SEQUENCE(COLUMNS($B3:$O3))))*SEQUENCE(COLUMNS($B3:$O3)))),999))),IF(COUNT(C3:$O3)=0,INDEX($B3:$O3,1,MAX((ISNUMBER(INDEX($B3:$O3,1,SEQUENCE(COLUMNS($B3:$O3))))*SEQUENCE(COLUMNS($B3:$O3))))),IFERROR(INDEX(C3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX(C3:$O3,1,SEQUENCE(COLUMNS(C3:$O3))))*SEQUENCE(COLUMNS(C3:$O3)))),999)))-(INDEX(C3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX(C3:$O3,1,SEQUENCE(COLUMNS(C3:$O3))))*SEQUENCE(COLUMNS(C3:$O3)))),999)))-INDEX(A3:$B3,1,MAX((ISNUMBER(INDEX(A3:$B3,1,SEQUENCE(COLUMNS(A3:$B3))))*SEQUENCE(COLUMNS(A3:$B3))))))*MIN(IFERROR(1/(1/(ISNUMBER(INDEX(C3:$O3,1,SEQUENCE(COLUMNS(C3:$O3))))*SEQUENCE(COLUMNS(C3:$O3)))),999))/(MIN(IFERROR(1/(1/(ISNUMBER(INDEX(C3:$O3,1,SEQUENCE(COLUMNS(C3:$O3))))*SEQUENCE(COLUMNS(C3:$O3)))),999))+COLUMN(C3)-COLUMN($B3)-MAX((ISNUMBER(INDEX(A3:$B3,1,SEQUENCE(COLUMNS(A3:$B3))))*SEQUENCE(COLUMNS(A3:$B3))))),0)))))
R3:AC6	R3	=IF(C3<>"",C3,IF(COUNT($B3:$O3)=0,"",IF(COUNT($B3:B3)=0,INDEX($B3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX($B3:$O3,1,SEQUENCE(COLUMNS($B3:$O3))))*SEQUENCE(COLUMNS($B3:$O3)))),999))),IF(COUNT(D3:$O3)=0,INDEX($B3:$O3,1,MAX((ISNUMBER(INDEX($B3:$O3,1,SEQUENCE(COLUMNS($B3:$O3))))*SEQUENCE(COLUMNS($B3:$O3))))),IFERROR(INDEX(D3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX(D3:$O3,1,SEQUENCE(COLUMNS(D3:$O3))))*SEQUENCE(COLUMNS(D3:$O3)))),999)))-(INDEX(D3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX(D3:$O3,1,SEQUENCE(COLUMNS(D3:$O3))))*SEQUENCE(COLUMNS(D3:$O3)))),999)))-INDEX($B3:B3,1,MAX((ISNUMBER(INDEX($B3:B3,1,SEQUENCE(COLUMNS($B3:B3))))*SEQUENCE(COLUMNS($B3:B3))))))*MIN(IFERROR(1/(1/(ISNUMBER(INDEX(D3:$O3,1,SEQUENCE(COLUMNS(D3:$O3))))*SEQUENCE(COLUMNS(D3:$O3)))),999))/(MIN(IFERROR(1/(1/(ISNUMBER(INDEX(D3:$O3,1,SEQUENCE(COLUMNS(D3:$O3))))*SEQUENCE(COLUMNS(D3:$O3)))),999))+COLUMN(D3)-COLUMN($B3)-MAX((ISNUMBER(INDEX($B3:B3,1,SEQUENCE(COLUMNS($B3:B3))))*SEQUENCE(COLUMNS($B3:B3))))),0)))))
AD3:AD6	AD3	=IF(O3<>"",O3,IF(COUNT($B3:$O3)=0,"",IF(COUNT($B3:N3)=0,INDEX($B3:$O3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX($B3:$O3,1,SEQUENCE(COLUMNS($B3:$O3))))*SEQUENCE(COLUMNS($B3:$O3)))),999))),IF(COUNT($O3:P3)=0,INDEX($B3:$O3,1,MAX((ISNUMBER(INDEX($B3:$O3,1,SEQUENCE(COLUMNS($B3:$O3))))*SEQUENCE(COLUMNS($B3:$O3))))),IFERROR(INDEX($O3:P3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX($O3:P3,1,SEQUENCE(COLUMNS($O3:P3))))*SEQUENCE(COLUMNS($O3:P3)))),999)))-(INDEX($O3:P3,1,MIN(IFERROR(1/(1/(ISNUMBER(INDEX($O3:P3,1,SEQUENCE(COLUMNS($O3:P3))))*SEQUENCE(COLUMNS($O3:P3)))),999)))-INDEX($B3:N3,1,MAX((ISNUMBER(INDEX($B3:N3,1,SEQUENCE(COLUMNS($B3:N3))))*SEQUENCE(COLUMNS($B3:N3))))))*MIN(IFERROR(1/(1/(ISNUMBER(INDEX($O3:P3,1,SEQUENCE(COLUMNS($O3:P3))))*SEQUENCE(COLUMNS($O3:P3)))),999))/(MIN(IFERROR(1/(1/(ISNUMBER(INDEX($O3:P3,1,SEQUENCE(COLUMNS($O3:P3))))*SEQUENCE(COLUMNS($O3:P3)))),999))+COLUMN(P3)-COLUMN($B3)-MAX((ISNUMBER(INDEX($B3:N3,1,SEQUENCE(COLUMNS($B3:N3))))*SEQUENCE(COLUMNS($B3:N3))))),0)))))

 

[Excelcomplicator]

Thank you very much for this.

it very much worked once I tweaked it for use in my spreadsheet.

brilliant!

 

[JamesCanale]

I'm impressed that you could take this muddled mess and make it work. It got out of hand.

When I made these kinds of things, I build them up in many cells, and then merge them all together. But it gets really hard to interpret then.