Linear or Polynomial Interpolation of yearly data to get monthly data in excel?

[joshnathan]

Hello,
How can i go about extracting monthly data from plotting yearly data?
I want to produce a linear or polynomial interpolation of 12 years of data and then produce a list of those monthly values.
Whats the easiest way of going about this.
I can provide data if that helps.

[MrShorty]

I see two basic approaches to this sort of problem: Curve fit the entire data set and use the resulting equation to interpolate, or extract an appropriate subset of the data and curve fit that subset. Here's how I tend to implement these two approaches:

1) Curve fit the entire data set. If I know what equation should fit the data, then I find this is the easier approach. Since polynomials are "linear" functions, the coefficients of a polynomial can be found using the LINEST() function http://office.microsoft.com/en-us/ex...005209155.aspx Be sure to review the help file so you understand how to enter the LINEST function as an array function (ctrl-shift-enter with the appropriate number of cells selected). They also have an example using a 3rd order polynomial that should be helpful to you.

2) If coming up with an equation for the entire data set is more difficult, then curve fitting a subset of the data may be preferred. The hardest part of extracting an appropriate subset of the data is locating the subset that will contain the month in question. Breaking this down into steps:
a) Locate the month of interest in the data set. I usually use the MATCH() function for this. http://office.microsoft.com/en-us/ma...837.aspx?CTT=1 What to use for each argument will depend on whether your "years" are input as text strings, year numbers, or date serials.
b) Once the desired month is located, then extract the appropriate subset. I will usually use the INDEX() function for this http://office.microsoft.com/en-us/ma...776.aspx?CTT=1 Recognize that you must extract at least one more data point than the order of the polynomial you intend to use for the interpolation (1st order/straight line polynomial requires at least 2 data points, quadratic/2nd order polynomial requires at least 3 data points, and so on).
c) Once you have extracted your subset of the data into another section of the spreadsheet, then you can apply the LINEST() function to the subset as described in option 1) and interpolate the monthly data.