I have numerous numbers in a column. I want to rank numbers from two
different ranges. Eg rows 2 - 50 and 150 - 200. i have tried selecting
these cells and using the rank formula but it doesnt work.

Can someone tell me if this is possible.

Thanks

Hi!

Use a named range.

Assume "rows 2 - 50 and 150 - 200" means A2:A50 and A150:A200.

Select the first range A2:A50. Hold down the CTRL key and select the second
range A150:A200.

In the Name box enter a name for that combined range. I'll use the name
"range".

Then in say, B2 enter this formula and copy down to B50:

=RANK(A2,range)

Then you would need to enter it again in B150:

=RANK(A150,range)

Biff

"Andrew" wrote in message
...
I have numerous numbers in a column. I want to rank numbers from two
different ranges. Eg rows 2 - 50 and 150 - 200. i have tried selecting
these cells and using the rank formula but it doesnt work.

Can someone tell me if this is possible.

Thanks

Assuming that your data are in column A (starting at A2), and you want the
rankings to go to column B, in B2 enter
=if(and(row(A2)50,row(A2)<150),"",rank(A2,($A$2:$ A$50,$A150:$A$200)))
and fill in the formula for the rest of the column B (i.e., till B200).
Please note that this formula will give equal ranks for ties.

B.R.Ramachandran

"Andrew" wrote:

I have numerous numbers in a column. I want to rank numbers from two
different ranges. Eg rows 2 - 50 and 150 - 200. i have tried selecting
these cells and using the rank formula but it doesnt work.

Can someone tell me if this is possible.

Thanks

copy both the ranges in some hidden column or sheet in one range, and
use this range to rank.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374980