Rank Question

[Andrew]

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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

[B. R.Ramachandran]

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
>

[mangesh_yadav]

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

Mangesh