| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the closed value?
There is a number 14 in cell A1, and there is a list of number under column B,
for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
Hi Eric, this should do it:
=INDEX($B$1:$B$5,LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*ROW($B$1:$B$5),),1)) If your list does not start in the first row, you would need to adjust like so: =INDEX($B$2:$B$6,LARGE(INDEX((ABS($B$2:$B$6-$A$1)= MIN(INDEX(ABS($B$2:$B$6-$A$1),)))*ROW($B$2:$B$6)- CELL("ROW",$B$2:$B$6)+1,),1)) HTH Steve D. "Eric" wrote in message ... There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
Scratch that, use this:
=LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,),1) "Steve Dunn" wrote in message ... Hi Eric, this should do it: =INDEX($B$1:$B$5,LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*ROW($B$1:$B$5),),1)) If your list does not start in the first row, you would need to adjust like so: =INDEX($B$2:$B$6,LARGE(INDEX((ABS($B$2:$B$6-$A$1)= MIN(INDEX(ABS($B$2:$B$6-$A$1),)))*ROW($B$2:$B$6)- CELL("ROW",$B$2:$B$6)+1,),1)) HTH Steve D. "Eric" wrote in message ... There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
Doh!
=MAX(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,)) "Steve Dunn" wrote in message ... Hi Eric, this should do it: =INDEX($B$1:$B$5,LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*ROW($B$1:$B$5),),1)) If your list does not start in the first row, you would need to adjust like so: =INDEX($B$2:$B$6,LARGE(INDEX((ABS($B$2:$B$6-$A$1)= MIN(INDEX(ABS($B$2:$B$6-$A$1),)))*ROW($B$2:$B$6)- CELL("ROW",$B$2:$B$6)+1,),1)) HTH Steve D. "Eric" wrote in message ... There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
Thank you very much for suggestions
What if I prefer the small number instead of large number, do you have any suggestions on how to do it? 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the small number, and 12 should be returned in cell A2. Thank you very much for any suggestions Eric "Steve Dunn" wrote: Scratch that, use this: =LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,),1) "Steve Dunn" wrote in message ... Hi Eric, this should do it: =INDEX($B$1:$B$5,LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*ROW($B$1:$B$5),),1)) If your list does not start in the first row, you would need to adjust like so: =INDEX($B$2:$B$6,LARGE(INDEX((ABS($B$2:$B$6-$A$1)= MIN(INDEX(ABS($B$2:$B$6-$A$1),)))*ROW($B$2:$B$6)- CELL("ROW",$B$2:$B$6)+1,),1)) HTH Steve D. "Eric" wrote in message ... There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
<grrr
For smaller figure that is closest: =LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,), SUMPRODUCT(--(ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),))))) For larger figure that is closest: =MAX(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,)) HTH Steve D. "Eric" wrote in message ... Thank you very much for suggestions What if I prefer the small number instead of large number, do you have any suggestions on how to do it? 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the small number, and 12 should be returned in cell A2. Thank you very much for any suggestions Eric "Steve Dunn" wrote: Scratch that, use this: =LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,),1) "Steve Dunn" wrote in message ... Hi Eric, this should do it: =INDEX($B$1:$B$5,LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*ROW($B$1:$B$5),),1)) If your list does not start in the first row, you would need to adjust like so: =INDEX($B$2:$B$6,LARGE(INDEX((ABS($B$2:$B$6-$A$1)= MIN(INDEX(ABS($B$2:$B$6-$A$1),)))*ROW($B$2:$B$6)- CELL("ROW",$B$2:$B$6)+1,),1)) HTH Steve D. "Eric" wrote in message ... There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
Thank you very much for suggestions
Eric "Steve Dunn" wrote: <grrr For smaller figure that is closest: =LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,), SUMPRODUCT(--(ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),))))) For larger figure that is closest: =MAX(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,)) HTH Steve D. "Eric" wrote in message ... Thank you very much for suggestions What if I prefer the small number instead of large number, do you have any suggestions on how to do it? 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the small number, and 12 should be returned in cell A2. Thank you very much for any suggestions Eric "Steve Dunn" wrote: Scratch that, use this: =LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,),1) "Steve Dunn" wrote in message ... Hi Eric, this should do it: =INDEX($B$1:$B$5,LARGE(INDEX((ABS($B$1:$B$5-$A$1)= MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*ROW($B$1:$B$5),),1)) If your list does not start in the first row, you would need to adjust like so: =INDEX($B$2:$B$6,LARGE(INDEX((ABS($B$2:$B$6-$A$1)= MIN(INDEX(ABS($B$2:$B$6-$A$1),)))*ROW($B$2:$B$6)- CELL("ROW",$B$2:$B$6)+1,),1)) HTH Steve D. "Eric" wrote in message ... There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to determine the closed value?
You're welcome Eric, glad to help.
Steve D. "Eric" wrote in message ... Thank you very much for suggestions Eric "Steve Dunn" wrote: |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup From Closed Worksheet | Excel Discussion (Misc queries) | |||
| Looking up information in closed files | Excel Discussion (Misc queries) | |||
| Closed Macros still visible | Excel Discussion (Misc queries) | |||
| Linking Files that are closed | Excel Discussion (Misc queries) | |||
| Value from a closed workbook | Excel Discussion (Misc queries) | |||
Linear Mode
