Rank function

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have a spreadsheet where I'd like to perform a 'RANK'
function. The problem is that I'd like to add an 'IF'
function to add a condition before I rank the values.

Example:

A B C
10 1
20 1
25 1
15 1
30 2
40 2
22 2
30 2

In this example, I'd like to rank the values in column A.
However, I'd like them to be ranked in each subgroup
(which is column B). So, the output of C should be:

A B C
10 1 4
20 1 2
25 1 1
15 1 3
30 2 2
40 2 1
22 2 4
30 2 2

Does anybody know how to do this?

Thanks a lot.
 
Hi Rob,

First, this tool is a part of the Analysis ToolPak. You will need to
install it first. If the Data Analysis command is not on the Tools menu,
you need to install the Analysis ToolPak in Microsoft Excel.

To install the Analysis ToolPak
On the Tools menu, click Add-Ins.
If Analysis ToolPak is not listed in the Add-Ins dialog box, click Browse
and locate the drive, folder name, and file name for the Analysis ToolPak
add-in, Analys32.xll - usually located in the Microsoft
Office\Office\Library\Analysis folder - or run the Setup program if it isn't
installed.

Select the Analysis ToolPak check box.

To use the Analysis ToolPak
On the Tools menu, click Data Analysis.
In the Analysis Tools box, click the tool you want to use.
Enter the input range and the output range, and then select the options you
want.

Wayne B
 
Let A1:B9 house the sample you provided, including labels:

{"Score","Group";10,1;20,1;25,1;15,1;30,2;40,2;22,2;30,2}

Option 1, *requires that the data is sorted on column B*:

In C1:E1 enter: {"Rank","Start","End"}, which is a set of labels/fields.

In C2 enter:

=RANK($A2,OFFSET($A$2,$D2-1,0,$E2-$D2+1,1))

In D2 enter:

=IF($B2<>$B1,MATCH($B2,$B$2:$B$9,0),$D1)

In E2 enter:

=IF($B2<>$B1,MATCH($B2,$B$2:$B$9),$E1)

Select C2:E2 and copy down as far as needed.

Option 2:

In C2 enter & copy down:

=MATCH($A2,LARGE(IF($B$2:$B$9=B2,$A$2:$A$9),ROW(INDIRECT("1:"&COUNTIF($B$2:$
B$9,$B2)))),0)

which you need to confirm with control+shift+enter, not just with enter.

Option 1 would be a better bet with large amounts of data. Someone I helped
with Option 2 has commented: Option 2 "works extremely well. The problem
however, is that the column that the formula is ranking has about 9500 rows.
It takes about 45 minutes to complete the calculations. It's a pain even
opening my file because of the long wait times. Any suggestions to speed up
the process?" Option 1 has been an alternative I came up with.
 
First, this tool is a part of the Analysis ToolPak. You will need to
install it first. If the Data Analysis command is not on the Tools menu,
you need to install the Analysis ToolPak in Microsoft Excel.
Click to expand...
...

?!

RANK is a built-in worksheet function. Conditional or ranking within groups has
been discussed before. See the following threads in the Google Groups archive.

http://www.google.com/[email protected]

http://www.google.com/[email protected]

http://www.google.com/[email protected]
 
I was taking it for granted that Rob was wanting to do an analysis, which is
a toolpak. Mis read or my tired eyes scanned over it.

Wayne B
 
Back
Top