RANK variation

  • Thread starter Thread starter Gill
  • Start date Start date
G

Gill

I'm trying to figure out if there's a way to rank a set
of numbers based on specified criteria.

To simplify my situation:
In a spreadsheet with three columns, Column A is a group
name such as Group 1, Group 2 etc. Column B is peoples
names, Column C is a figure (total projects completed for
example)

I would like to add a RANK figure in Column D based on
the persons group. For example - =RANK(C1, C1:C? where
A1:A? = Group 1) sort of thing.

Got any ideas? I'm happy to try anything.
 
Excel has a RANK function for the purpose you need it.


Formula would be = Rank(number_ref_Order)
Number is the number you want ranked- Ref is the array
where the number is to be ranked and order lets you sort
it ascending descending.

RED
 
Here are two different setups, you can try.

Assumptions
Data in columns A:C starting in row 2.
for 1: Groupname in F1.

1:
In D2 enter this formula:

=(COUNTIF($A$2:$A$25,$F$1)-SUMPRODUCT((C2>$C$2:$C$25)*
($A$2:$A$25=$F$1))-SUMPRODUCT(($A$2:$A$25=$F$1)*
($C$2:$C$25=C2))+1)*(A2=$F$1)

Copy down with the fill handle.

2:
In E2 enter this formula:

=(COUNTIF($A$2:$A$25,A2)-SUMPRODUCT((C2>$C$2:$C$25)*
($A$2:$A$25=A2))-SUMPRODUCT(($A$2:$A$25=A2)*($C$2:$C$25=C2))+1)

Copy down with the fill handle.
 
Hi Gill,

Groups: Range in Column A
Scores: Range in Column C
In D1:
=SUMPRODUCT((Groups=A1)*(Scores>C1))+1

Regards,

Daniel M.
 
Neat, Daniel! How could I miss that one :-)
I must be getting too old for this :-)

Regards
LeoH
 
Back
Top