Interpolate missing data

Your question sounds very similar to this discussion I participated in: http://www.excelforum.com/showthread.php?t=1145387 Note that the strategy I used was to remove the blanks from the source table to create a separate lookup table. Then perform the interpolation using the lookup table. Review the discussion and examples, and let us know what part you don't understand or get stuck on.

What about step 2 was confusing? Did you follow the link to the help files for the functions so you would know how to use them? Or was there something else about using those functions that you found confusing.

As an example of how I would use these functions for this:
1) I might enter =SLOPE(B2:B3,A2:A3) in E2 and INTERCEPT(B2:B3,A2:A3) in F2 to get slope and intercept for the first entry. Note the relative referencing. Copy those formulas down to get slope and intercept for each entry. You might need to adjust the references for the very last entry, if you need to extrapolate beyond the last date. This table will be used as the lookup table to perform the interpolations.
2) Create a second table where I perform the interpolation. Perhaps in I2, enter 1/4/1999, in I3, enter I2+1 (again, note relative references) and copy I3 down as far as needed.
3) Slope for each day is a simple lookup function =VLOOKUP(I2,$A$2:$F$4000,5,TRUE). A similar function returns the INTERCEPT(). Note the mix of relative and absolute references, and that I used TRUE for the 4th argument of the VLOOKUP() function. https://support.office.com/en-us/art...8-93a18ad188a1
4) From the slope and intercept, the linear interpolation is a simple y=mx+b function.

This proposed solution uses more than a few helper columns. I know that it is policy to show the formulas used however since there were so many I am going to generally explain the way I came up with the Column of Interpolated Values. First I made a column of all dates between 1/4/1999 and 1/1/2014. I did this by putting 1/4/1999 in F2. I then held the right mouse button while pulling the fill handle of F2 down and then up, chose series from the pop up box, chose columns, and put 1/1/2014 in the stop value box. I then reduced that list, in the next column, to only weekdays as per post #1. The next column uses the INDEX function to match the closing price to the date. The next column gives the average for closings that could go in the places where only one cell is left blank between closings. The next column gives a list of closings, the originals as well as the averages placed in cells that were a single date's closing was skipped (I hope that makes sense). The next three columns are used to identify the number of rows that left blank so that the formula 'Average per blank' column will have that information. The 'average per blank' column will take the difference in the closing on the dates before and after the skip and divide by the number of rows skipped plus one. The 'Interpolated Column' then displays all of the values for all weekdays between 1/4/199 and 1/1/2014.
If you decide to use this solution you may want to hide all of the helpers, basically all of the columns except G and O.
Let us know if you have any questions.