Extrapolating data within a table

Good morning,

I've been tasked with fixing up a spreadsheet to cut down a lot of time. I've found a UDF that can interpolate my data easily ( http://www.dailydoseofexcel.com/arch...ookup-between/ ).

My table has several columns and all but one are filled out to the extent of the X data, where as one column is cut off at about half way through. I'd like to extrapolate the incomplete data based on the last two points of information to the range of the X data and then I can use the interpolate function to grab any data inbetween. I know I can do this using the trend() function but is there a way to do it with a macro so anyone else using the spreadsheet can plug in their table of information (which may be have more or less lines of data)?

I've attached an example table -- I'm looking to extrapolate the C values to the range of the X values based on the last two available C points of data. Is this possible/if so is there an existing macro that I could use?

Thanks in advance for your help and I apologise if this is considered a really stupid question... but I learned a tiny bit of VB years ago and am simply not very familar with all of the Excel VB functions.

Just wondering if anyone knows if this is possible?

Originally Posted by shg

To extrapolate linearly from the last two points, in D10 and copy down,

=$D$9 + (A10-$A$9) * ($D$9-$D$8) / ($A$9-$A$8)

shg,

Thanks for your suggestion. I'm aware of how to extrapolate based on this case but the inputted data in that table may have more or less points of data so your suggested formula won't work unless there are exactly this many points of data.

Is it possible to extrapolate not knowing how many points of data there will be?

Would it work if you took out the absolute references ($)? =$D9 + (A10-$A9) * ($D9-$D8) / ($A9-$A8). Where all of the extrapolated points are collinear with the last two entered values, linear extrapolation based on any two of the points (including the previous two in the series), should yield the same result. Then when someone copies in a larger table than original, the formula won't need to be adjusted. The tricky situation is how to deal with the case when someone copies in a smaller table than given. I don't know exactly how I would handle that in your situation -- maybe build a "template spreadsheet" with the smallest possible entered table (two rows). Then train users to always start with that template when entering their own data.