How to determine the value?

[Eric]

Does anyone have any suggestions on how to determine the closest number
without using sorting functions?

Example One
There is a list of number under column A
191,189,183,177,175,171,167,165,159,153,151
and there is a given number in cell B1, 154.
I would like to determine the closest number, which match with the number in
cell B1. On above example, it should return 153 in cell C1.

Example Two
There is a list of number under column A
191,189,183,177,175,171,167,165,155,153,151
and there is a given number in cell B1, 154.
I would like to determine the closest number, which match with the number in
cell B1. On above example, if the given number matches two numbers, which the
difference is the same, then it should return 153 in cell C1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Mike H]

Eric,

=INDEX(A1:A11,MATCH(FALSE,B1<A1:A11,0))

or if you want to return the larger value in the event of a tie

=INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),))

These are array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.


Mike

 

[David Biddulph]

Please don't multipost.

 

[Mike H]

David,

Agree totally with the irritations of multi-posting but I can't see another
post. Where is it?

Mike

 

[David Biddulph]

On microsoft.public.excel.misc

today, after a
similar
on Nov 5th

I think you answered the one on Nov 5th, Mike?

 

[Mike H]

David,

Had to find this in Forte Agent ( I normally use the web interface). I think
you'll find that when the OP asked this very similar question previously in
the event of a tie the higher value was to be returned, this time it's the
lower value.

Mike

 

[Eric]

Thank you very much for suggestions
Whatif I want to be a little bit complicated.

Example Three
There is a list of number under column A
193,191,185,179,177,173,169,167,161,155,153,149,145
and there is a given number in cell B1, 152.
The difference between 153 and 152 is 1,
but the difference between 152 and 149 is 3
I would like to return 153 in cell C1, because it is the closest value from
152.

Example Four
There is a list of number under column A
193,191,185,179,177,173,169,167,161,155,153,149,145
and there is a given number in cell B1, 151.
The difference between 153 and 151 is 2,
but the difference between 151 and 149 is 2
I would like to return 149 in cell C1, because it is the lower value from 151.

Example Five
There is a list of number under column A
193,191,185,179,177,173,169,167,161,155,153,149,145
and there is a given number in cell B1, 1.
In this case, it will return #N/A in cell C1
I would like to return 145 in cell C1, because it is the lowest value from
the list.

Does you have any suggestions?
Thank you very much for any suggestions
Eric

 

[Mike H]

Hi,

I thibnk your relcuctance to sort is making lfe very hard for yourself. Give
up and sort the range and then it becomes easy. If you dont want to sort the
actual range then copy it to somewhere else and sort it leaving the original
data intact.

If you do that
=INDEX(A1:A13,MATCH(MIN(ABS(A1:A13-B1)),ABS(A1:A13-B1),))

Does what you want

Mike

 

[Eric]

Thank you very much for suggestions

When I type 151 in cell B1, it returns 153 instead of 149.
Do you have any idea on how to solve it?

Example Four
There is a list of number under column A
193,191,185,179,177,173,169,167,161,155,153,149,145
and there is a given number in cell B1, 151.
The difference between 153 and 151 is 2,
but the difference between 151 and 149 is 2
I would like to return 149 in cell C1, because it is the lower value from 151.

Thank you very much for any suggestions
Eric