Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the closest number in column A and take the value in column B
hi guys! I've been trying to do the next without success. i'm desperate! The user type a number in cell J5 ... The function has to look for the closest larger value in column A then it takes the value that is in the same row but in the column B and copy this value in the cell K5. The table is: 0.5000 0.0030 0.6660 0.0085 1.0000 0.0220 1.5000 0.0420 2.0000 0.0560 2.5000 0.0630 3.0000 0.0670 the user type for example 1.25 in cell J5 and the function gets 0.0420 in K5 Thanks for your help. -- reefguy ------------------------------------------------------------------------ reefguy's Profile: http://www.excelforum.com/member.php...fo&userid=8282 View this thread: http://www.excelforum.com/showthread...hreadid=539167 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the closest number in column A and take the value in column B
Hi!
Here's one way: Assume the table is in the range A1:B7. Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ISNUMBER(J5),INDEX(B1:B7,MATCH(TRUE,A1:A7J5,0 )),"") If the value emtered in J5 is = the max value in column A you'll get an #N/A! error. Biff "reefguy" wrote in message ... hi guys! I've been trying to do the next without success. i'm desperate! The user type a number in cell J5 ... The function has to look for the closest larger value in column A then it takes the value that is in the same row but in the column B and copy this value in the cell K5. The table is: 0.5000 0.0030 0.6660 0.0085 1.0000 0.0220 1.5000 0.0420 2.0000 0.0560 2.5000 0.0630 3.0000 0.0670 the user type for example 1.25 in cell J5 and the function gets 0.0420 in K5 Thanks for your help. -- reefguy ------------------------------------------------------------------------ reefguy's Profile: http://www.excelforum.com/member.php...fo&userid=8282 View this thread: http://www.excelforum.com/showthread...hreadid=539167 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the closest number in column A and take the value in column B
Thanks! It work perfect... One more question; *in the same table*, what function I need in the cell K4 to get the number that is below the number that I got in cell K5 For example the user types 1.75 then I got 2.0000 in the cell K5 (thanks Biff) ... how do I get 1.5000 in the cell K4? I tried to change "" for "<" in the formula but I got always 0.0030 in the cell K4. Thanks -- reefguy ------------------------------------------------------------------------ reefguy's Profile: http://www.excelforum.com/member.php...fo&userid=8282 View this thread: http://www.excelforum.com/showthread...hreadid=539167 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the closest number in column A and take the value in column B
Hi!
Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ISNUMBER(J5),INDEX(B1:B7,MATCH(TRUE,A1:A7J5,0 )-1),"") Again, if the value in J5 = the max value in column A you'll get an #N/A error. Also, if the value in J5 < min value in column A you'll get the min value. We could make this more robust if you were to tell me the limits of the user entered value of J5. For example, is 0.49 a value that can be entered in J5? If so, in the table there is a value 0.49 but there is no value < 0.49. Same with 3.0. there is a value < than 3.0 but there is no value 3.0. Biff "reefguy" wrote in message ... Thanks! It work perfect... One more question; *in the same table*, what function I need in the cell K4 to get the number that is below the number that I got in cell K5 For example the user types 1.75 then I got 2.0000 in the cell K5 (thanks Biff) ... how do I get 1.5000 in the cell K4? I tried to change "" for "<" in the formula but I got always 0.0030 in the cell K4. Thanks -- reefguy ------------------------------------------------------------------------ reefguy's Profile: http://www.excelforum.com/member.php...fo&userid=8282 View this thread: http://www.excelforum.com/showthread...hreadid=539167 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|