Ranking if number is between 550 and 855.
 

I need to know how to rank a list of numbers but only rank the numbers
between 550 and 855
Example
A
523
705
557
890
I need to rank 523, 705, 890, and leave 523 and 890 out...and in desending
order.
So the ranking should be 705:2, and 557:1
Thanx.

Ranking if number is between 550 and 855.
 

I assume:

between 550 and 855

is inclusive, meaning =550 and <=855

Try this:

=IF(AND(A1=550,A1<=855),SUMPRODUCT(--(A$1:A$4=550),--(A$1:A$4<=855),--(A1A$1:A$4))+1,"")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"David A." wrote in message
...
I need to know how to rank a list of numbers but only rank the numbers
between 550 and 855
Example
A
523
705
557
890
I need to rank 523, 705, 890, and leave 523 and 890 out...and in desending
order.
So the ranking should be 705:2, and 557:1
Thanx.

Ranking if number is between 550 and 855.
 

=IF(OR($A1<550,$A1855),"",MATCH(A1,LARGE(IF(($A$1 :$A$4=550)*($A$1:$A$4<=855),$A$1:$A$4),ROW(INDIRE CT("1:"&SUMPRODUCT(--($A$1:$A$4=550),--($A$1:$A$4<=855))))),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"David A." wrote in message
...
I need to know how to rank a list of numbers but only rank the numbers
between 550 and 855
Example
A
523
705
557
890
I need to rank 523, 705, 890, and leave 523 and 890 out...and in desending
order.
So the ranking should be 705:2, and 557:1
Thanx.