Interpolating multiple points between known values

[gumpstump]

Hello, I have a question about interpolation. My problem is this: I have some data from a geophysics survey in a simple xlsx sheet (Excel 2010). The survey data consists of type 1 data taken at stations of known locations (X and Y coordinates) along a line (flagged physically in the field), and type 2 data collected every 0.5 seconds by a surveyor walking between stations along this line. The type 1 data is only taken at stations, and these station locations are known (as they are flagged on the ground). My problem is applying X/Y coordinates to the type 2 data. The surveyors walk at a steady pace between stations, but due to differing terrain they may be faster or slower; thus there may be as little as 10 readings or as many as 30 readings between stations. Because they walk at a steady pace it can be assumed that the number of readings between stations is equally spaced between the stations. I need to apply X/Y coordinates to a set of type 2 data, and equally space it between two known X/Y stations.

Short version of the problem: Is there any way to interpolate a given number of equally spaced X/Y coordinates between two known X/Y coordinates in excel? I'm only aware of interpolation in excel being able to do one point at a time. Thanks!

[djapigo]

Will the formula TREND work for you?

[gumpstump]

It could be part of the solution, but TREND still requires a known X input. That would mean manually dividing the change in X (between stations) by the number of inter-station readings, then putting those Xs into TREND to get the corresponding Y coordinates. That would be a ton of manual work, unless there's some way to shortcut that? I'm not an expert in excel, so I'm not sure.

[djapigo]

Hi gumpstump.

Can you provide some sample data? And a sample result of what you want to see.

Please attach a spreadsheet (click on Go Advanced) if possible. You can send made up data if needed.

Thank you,
Dennis

[gumpstump]

Okay, I've attached some sample data. There are eight stations in there along one line, with numerous readings between each station (roughly 20-30 between each). I am trying to increment the X/Y to fill this table with coordinate data (where the coords are neatly incremented between stations). Because there are a non-standard number of readings between stations (and thus a nonstandard change in X and Y between each reading), it seems difficult to automate the process.

Example_data.xlsx

[WHER]

See if these results are what you expected to see. Column I and J use the function "Linterp", a User Defined Function by SHG.

[djapigo]

Hi gumpstump,

Solution1: I used this proration formula in B3: =B2+(B$32-B$2)/30 for the first section... you just have to coordinate for B33 since there 38 to prorate...

Solution2: Another neat feature is to highlight the cells B2:B32, go to the Home tab, Editing section, pull down on Fill, choose Series... put a check mark on Trend, choose either Linear or Growth, press OK...

Both have their faults... Solution1 the formula changes for each "section"... while Solution2 can only work one "section" at a time...

WHER's solution looks good too... nice work!

Take care,
Dennis

[gumpstump]

Thanks so much! Sorry for the late reply but I had a long-long-weekend off. I used WHER's system, and got the LInterp UDF from this thread:

http://www.excelforum.com/excel-form...-min-data.html

Thanks again!