How to get the numbers above and below a number

[Raz]

Hi, Thanks in advance if you can help.

I need two seperate equations to find the number above and below a specific
number from a list of numbers in accending order.

for example my specific number is 875, I need to find the number (only one
number) which is immediately less than 875, and the number that is
immidiately higher than 875 from the list below.

The two equations should give me 850 and 900 in seperate cells.

500
550
600
650
700
750
800
850
900
950
1000

 

[Mike H]

Hi,

Try this for the lower number

=VLOOKUP(B1,A1:A11,1,TRUE)

and this for the higher

=MIN(IF(A1:A11>=B1,A1:A11))

This is an array formula which must be entered with 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 ranges
'then you must re-enter as An array

In the event of an exact match these both return the match


Mike

 

[T. Valko]

Try these array formulas** :

C2 = 875

For the next lowest number (if there is one):

=MAX(IF(A2:A12<C2,A2:A12))

For the next highest number (if there is one):

=MIN(IF(A2:A12>C2,A2:A12))

If there isn't a next lowest/highest the respective formula will return 0.
For example, lookup 1200. There is no next highest so that formula will
return 0. The next lowest would be 1000. Lookup 490, there is no next lowest
so that formula would return 0. The next highest would be 500.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Glenn]

Hi, Thanks in advance if you can help.

I need two seperate equations to find the number above and below a specific
number from a list of numbers in accending order.

for example my specific number is 875, I need to find the number (only one
number) which is immediately less than 875, and the number that is
immidiately higher than 875 from the list below.

The two equations should give me 850 and 900 in seperate cells.

500
550
600
650
700
750
800
850
900
950
1000

Assuming your numbers in A1:A11 and 875 is in C4, array-enter (commit with
CTRL+SHIFT+ENTER) these two formulas:

=MAX(IF(A1:A11<C4,A1:A11,""))

=MIN(IF(A1:A11>C4,A1:A11,""))

 

[Raz]

Thanks Mike, they work just perfectly.
Wondering if it is possible to do these by simple eqations (i mean not
having these curly brackets)

 

[Jarek Kujawa]

for some time now I have not been able to see new posts
only answers to them
can anyone pls explain why?
am using Internet Explorer
thks

 

[Mike H]

Raz,

Not that I know of. I avoided an array in 1 of the formula by using vlookup.

Thanks for the feedback

Mike

 

[T. Valko]

There was a problem where posts made through the MS web interface have not
been getting picked up by news readers and other applications. For example,
I use Outlook Express to access the groups and since last Wed/Thur I was not
getting any posts made through the web interface. I only got posts made by
other news readers and Google Groups.

This seems to have been corrected sometime yesterday afternoon.

All seems to be working normally for the time being.

--
Biff
Microsoft Excel MVP


for some time now I have not been able to see new posts
only answers to them
can anyone pls explain why?
am using Internet Explorer
thks

 

[T. Valko]

Will the number you're looking for *always* be within the range of your
listed numbers? In other words, there will never be a lookup value less than
the min value in the list and there will never be a lookup value greater
than the max value in the list.

 

[Jarek Kujawa]

thanks Biff
nothing seems to have changed for me till now
;-(

 

[Ron Rosenfeld]

Hi, Thanks in advance if you can help.

I need two seperate equations to find the number above and below a specific
number from a list of numbers in accending order.

for example my specific number is 875, I need to find the number (only one
number) which is immediately less than 875, and the number that is
immidiately higher than 875 from the list below.

The two equations should give me 850 and 900 in seperate cells.

500
550
600
650
700
750
800
850
900
950
1000

What do you want to return if the Specific Number is exactly equal to a member
in the List of Numbers?

To return either the Same number (if Specific Number is present in the List) or
the next lowest number (if Specific Number is NOT present in the list):

=VLOOKUP(SpecNum,ListOfNums,1)

To return the next higher number:

=INDEX(ListOfNums,1+MATCH(SpecNum,ListOfNums),1)

The formulas will return error messages if specific number is not within the
range of the List of Numbers.
--ron