How to determine the closed value?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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
 
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.
 
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)
 
Doh!

=MAX(INDEX((ABS($B$1:$B$5-$A$1)=
MIN(INDEX(ABS($B$1:$B$5-$A$1),)))*$B$1:$B$5,))
 
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
 
<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.
 
Thank you very much for suggestions
Eric

Steve Dunn said:
<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.
Click to expand...
 
Back
Top