# Extrapolating data within a table

1. ## 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.

2. ## Re: Extrapolating data within a table

Just wondering if anyone knows if this is possible?

3. ## Re: Extrapolating data within a table

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)

4. ## Re: Extrapolating data within a table

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?

5. ## Re: Extrapolating data within a table

I don't know enough about what you're trying to do; I answered the question for the data you provided. Can you not change the references if the data ends in a different row?

6. ## Re: Extrapolating data within a table

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.

7. ## Re: Extrapolating data within a table

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.
Yes indeed.

8. ## Re: Extrapolating data within a table

You could instead use in D10,

=TREND(D8:D9, A8:A9, A10)

Note that just references to last two y values, the last two x values, and the new x.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1