On Mon, 15 Nov 2004 21:10:31 -0600, geooil
wrote:


Hi,

I have a list a numbers with two columns (depth and time). I also have
another depth (d1) value and I would like to use to look up the list and
find the nearest 2 depth values so I can use them to find the
correlating time value (t1) for (d1)

eg
d1 = 1200

Time Depth
0.00 0.00
223.40 -325.00
381.80 -525.00
551.00 -735.00
677.40 -925.00
842.00 -1175.00
952.20 -1373.80
970.00 -1425.00
1058.40 -1675.00


I would like to be able to look up the list using d1 and have it return

842.00 -1175.00
952.20 -1373.80

so I can use a linear equation to work out what the correlating time
for d1 is.

Can someone please help me work out which functions are the best to
use.

Thanks

Let's back up a bit. You say your goal is to correlate the depth in D1 with
the time based on a linear equation.

A different approach would be to use a formula to draw a best fit line through
the data, and obtain your time based on that line.

If the line through all of the data should be a straight line, then the
formula:

=TREND(Time,Depth,D1)

where Time and Depth are the named ranges containing that data from your table,
and D1 is entered as a NEGATIVE number. (Or you could enter it as a positive
number and just reverse the sign in the equation).

Visually, though, the data appears to best fit a third order polynomial (with
an R2 of 0.999). Using that to generate the equation, one comes up with:

=SUMPRODUCT(D1^{3,2,1,0},LINEST(Time,Depth^{1,2,3} ))

so -1200 -- 858.28

You have to determine what kind of line best describes the data you have.


--ron