Non linear but linear Interpolation

cyglor

New Member
Joined
Feb 14, 2016
Messages
9
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,149
Office Version
365
Platform
Windows
Hi Cyglor,
could you maybe post a chunk of your table (say the first 50 rows) and some desired outcomes?
Thanks,
Koen
 

cyglor

New Member
Joined
Feb 14, 2016
Messages
9
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

New Member
Joined
Feb 14, 2016
Messages
9
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.
 

Forum statistics

Threads
1,081,526
Messages
5,359,279
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top