Interpolating values between two dates

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...dates.1212717/ and https://techcommunity.microsoft.com/...es/m-p/3591420)

I expect there are details here (about the data or the desired solution/results) that are missing because of a screenshot rather than a sample file. For example, your screenshot appears to show only the monthly data, but your post talks about both quarterly and monthly data. Interpolation problems, though, are fairly common around here.

This Excel community continues to tolerate my advocating for a different spreadsheet. If you are allowed to use a different spreadsheet, Gnumeric has a built in INTERPOLATION() function that is usually easier to implement than anything in Excel.

If you are limited to Excel, then you have to build your own interpolation sequence of functions. IMO, the hardest part of building your own interpolation algorithm is the lookup step. I outline how I would typically do this here: https://www.excelforum.com/excel-for...ml#post5380131 (the OP puts a file together later in the thread). Your screenshot shows data for 2 different "firms" which might suggest that your lookup will be more complicated as you not only need to lookup date, but also firm. If each firm always uses the same dates in column 2, then a pivot step (probably easiest using power query/Get and Transform) to arrange the data into a 2D table might be easiest. I'm also kind of assuming that the quarterly data looks substantially similar to the monthly data. In a general outline, I would expect to:

1) If deemed necessary, rearrange data to simplify the lookup step.
2) Calculate slope and intercept for each pair of points (for each firm) as part of the lookup table.
3) A lookup function (usually using approximate match option) to return slope and intercept at the specified date from the monthly table.
4) Solve y=mx+b to interpolate.