Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ---- |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding to the nearest 9th | Excel Discussion (Misc queries) | |||
How do I roundup to the nearest 9 | New Users to Excel |