A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to determine the closed value?



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 08:48 AM posted to microsoft.public.excel.misc
Eric
external usenet poster
 
Posts: 1,956
Default 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  
Old June 2nd, 2010, 10:15 AM posted to microsoft.public.excel.misc
Steve Dunn
external usenet poster
 
Posts: 192
Default 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  
Old June 2nd, 2010, 10:24 AM posted to microsoft.public.excel.misc
Steve Dunn
external usenet poster
 
Posts: 192
Default 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  
Old June 2nd, 2010, 10:26 AM posted to microsoft.public.excel.misc
Steve Dunn
external usenet poster
 
Posts: 192
Default 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  
Old June 2nd, 2010, 12:55 PM posted to microsoft.public.excel.misc
Eric
external usenet poster
 
Posts: 1,956
Default 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  
Old June 2nd, 2010, 01:39 PM posted to microsoft.public.excel.misc
Steve Dunn
external usenet poster
 
Posts: 192
Default 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  
Old June 2nd, 2010, 02:36 PM posted to microsoft.public.excel.misc
Eric
external usenet poster
 
Posts: 1,956
Default 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  
Old June 2nd, 2010, 02:53 PM posted to microsoft.public.excel.misc
Steve Dunn
external usenet poster
 
Posts: 192
Default 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:


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.