looking up nearest value

G

geooil

Hi,

I have a list a numbers with two columns (depth and time). I also hav
another depth (d1) value and I would like to use to look up the list an
find the nearest 2 depth values so I can use them to find th
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 retur

842.00 -1175.00
952.20 -1373.80

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

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

Thank
 
M

Myrna Larson

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
 
M

Max

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top