Ranking if number is between 550 and 855.

[David A.]

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.

 

[T. Valko]

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),--(A1>A$1:A$4))+1,"")

Copy down as needed.

 

[Bob Phillips]

=IF(OR($A1<550,$A1>855),"",MATCH(A1,LARGE(IF(($A$1:$A$4>=550)*($A$1:$A$4<=855),$A$1:$A$4),ROW(INDIRECT("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)