Dynamic interpolation/extrapolation

Excelcomplicator

New Member
Joined
Oct 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

1634810224201.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
461
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
This got silly.
Cell Formulas
RangeFormula
C2:O2C2=EOMONTH(B2,0)+1
Q2:AD2Q2=B2
Q3:Q6Q3=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:AC6R3=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:AD6AD3=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)))))
 
Solution

Excelcomplicator

New Member
Joined
Oct 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for this.

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

brilliant!
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
461
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,571
Messages
5,770,920
Members
425,652
Latest member
Pemby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top