using forecast offset and match function for data that has blank values

[larchonka]

Hi guys,

I am trying to interpolate missing values using forecast function but i get #DIV/0! error as the data I am using for interpolation has missing values.

underlying data:

VALUE 1 VALUE2
2013
2014 1.3189164
2015 1.3963318 1.8419541
2016 1.5215546
2017 1.9101192
2018 2.8905205
2019 2.5454719 3.4491738
2020 2.8423608 3.6531683
2021 3.8870447
2022 3.3015105
2023 2.895862 4.2576547


interpolated result:

YEAR INTERPOLATED INTERPOLATED2
2013 #DIV/0! #DIV/0!
2014 1.32 #DIV/0!
2015 1.40 #DIV/0!
2016 1.52 #DIV/0!
2017 #DIV/0! #DIV/0!
2018 #DIV/0! 2.89
2019 2.55 3.45
2020 #DIV/0! 3.65
2021 #DIV/0! #DIV/0!
2022 3.30 #DIV/0!
2023 #DIV/0! #DIV/0!

fromula i am trying to use =FORECAST(A2,OFFSET(Sheet1!$B$2:$B$32,MATCH(A2,Sheet1!$A$2:$A$32,1)-1,0,2),OFFSET(Sheet1!$A$2:$A$32,MATCH(A2,Sheet1!$A$2:$A$32,1)-1,0,2))

Would greatly appreciate any advice

[arthurbr]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[larchonka]

hey Arthur, I have attached the file. Many thanks

[larchonka]

to give a bit of background, I have the yield data in before tab which has bond yields for respective maturities, I want to build a yield curve on the after tab by using linear interpolation but using actual yield values in before tab to build it. Alternatively I could use the data with no blanks but in this case the range that forecast function is referring to has to be conditional on a defined criteria.