looking up nearest value
 

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


--
geooil
------------------------------------------------------------------------
geooil's Profile: http://www.excelforum.com/member.php...o&userid=16502
View this thread: http://www.excelforum.com/showthread...hreadid=278897

The list must be sorted in descending order on column B, as you show it, but
without the 0's in the first row.

The first formula returns 842, the 2nd 952.2.

=INDEX($A$2:$A$9,MATCH(-D1,$B$2:$B$9,-1))
=INDEX($A$2:$A$9,MATCH(-D1,$B$2:$B$9,-1)+1)

You can return the values from column B by replacing the $A$2:$A$9 with
$B$2:$B$9

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

One way ..

Assume the table below is in Sheet1, A1:B10

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

Put in C2: =ABS(B2)
Copy down to C9

In Sheet2
-------------
Put the labels in A1:C1 : d, Time, Depth

Enter the depth in A2: 1200 (say, enter w/o the negative sign)

Put in B2: =INDEX(Sheet1!A:A,MATCH($A2,Sheet1!$C:$C,1))

Put in B3:
=IF(ISNA(MATCH($A2,Sheet1!$C:$C,0)),INDEX(Sheet1!A :A,MATCH(B2,Sheet1!A:A,0)+
1),"")

Select B2:B3 and copy across to C3

The above will return (in the output range B2:C3)
the Time and Depth from the table in Sheet1
for the d-value input in A2

If the d-value input in A2 coincides exactly
with a Depth value listed in B2:B10 in Sheet1,
blanks will be returned in the 2nd row
of the output range, i.e. in B3:C3

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"geooil" wrote in message
...

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


--
geooil
------------------------------------------------------------------------
geooil's Profile:
http://www.excelforum.com/member.php...o&userid=16502
View this thread: http://www.excelforum.com/showthread...hreadid=278897

Put in C2: =ABS(B2)
Copy down to C9

Typo, sorry .. line above should read:
Copy down to C10

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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