Non linear but linear Interpolation

[cyglor]

If my title caught your attention, please read further. I am not looking for elegance, but I am looking for a fast way to go through the data.
I have 19914 lines (yes 19914 rows) of data that are separated into 3 columns
Column A: Known Depth
Column B: Known Age
Column C: Known Depth (but points fall in between those in column A)
Column D: Would like to know the ages for the depths in Column C using interpolation with columns A and B.

So, I have columns A and B, ages matched to depths. I have a third column with a depth, but no age. I would like to know the age of the depths in this column. The data is linear, but the linear part (slope) fluctuates throughout the data set. Excel freaks out and interpolates the data in both ascending and descending orders instead of just descending (ages go back in time as depth value gets higher).

I figured the easiest way to get the correct age would be to find the two points in column A where my depth in column C falls in between. Excel freaked out again because while the mid depths (column C) are rather evenly spread, the depths in column A are not.

So, is there anyone out there that can come up with a series of codes that will help, I would be forever grateful.

 

[Rijnsent]

Hi Cyglor,
could you maybe post a chunk of your table (say the first 50 rows) and some desired outcomes?
Thanks,
Koen

 

[AhoyNC]

Maybe one of these links will help.

Linear Interpolation with Excel
Excel Interpolation Formulas - Peltier Tech Blog
Microwaves101 | Linear Interpolation for Excel

 

[cyglor]

I would love to post a table, but sadly, that doesn't come with the free version. So, hmmm....
Column A: Depth from 5.0m to 8.0m, then add .003-.006cm to every line. The original data isn't uniform, so just randomly pick a number between .003 and.006.
Column B: Age, in decimal form. From 2010-1988. Again, the data isn't uniform so just randomly put numbers in, as long as they go down as depth increases.
Column C: Depth...again. This time, make the numbers fall in between two of the numbers in column A. This is uniform, start at the top and go every .005m.
Column D: Aga... again. This age is for those depths in column C.

Basically, you are using columns A and B to age Column C and putting those ages in column D.

I hope this helps some. I am sorry, but I can't copy and paste or even add attachments. Free version.

 

[cyglor]

I was FINALLY able to get the interpolation to work... sort of. Since my data is not uniform by any means, I had to make some adjustments. But here is the final code.
I ended up adjusting the data a little as well.
Column A: Original Depths, matched to years in Column C.
Column B: New depths... the ones that are not dated.
Column C: The dates that match to depths in column A. Actually starts at row 12, the upper levels were not dated, so I started here.

Column E: =MATCH((B5),(A2:$A$19940))+G5
This matches the depth in column B to the lower depth from Column A.

Column G is simply a string of numbers starting at 1 and adding one to each cell below it. This is to help balance the data because of its non uniformity.
G5=1, G6=2, G7=3, G8=4... etc. This was done to compensate for my not uniform depths in column A.

And the final piece... the year for my depths in column B.
Column F: =INDEX($C$12:$C$19940,E5) +(B5-INDEX($A$2:$A$19940,E5)) *(INDEX($C$12:$C$19940,E5+1)-INDEX($C$12:$C$19940,E5))/(INDEX($A$2:$A$19940,E5+1)-INDEX($A$2:$A$19940,E5))+0.4

The extra 0.4 is also a needed correction factor as my interpolated depths were continually running on the low side.
As you can see, there are A LOT of rows in my data, which is why I wanted something that works and not necessarily elegant.

Please don't make the obvious comment about needing the correction factors because my rows don't exactly line up. They aren't meant to. For example, the bottom depth of 300 meters falls at line 19940 in the original dating. My new depths stop at row 7023, also at 300meters. So, the correction factors are meant to correct for the interpolation math, not the unaligned data.