RANK variation

[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.

 

[red bardes]

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

 

[Leo Heuser]

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.

 

[Daniel.M]

Hi Gill,

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

Regards,

Daniel M.

 

[Leo Heuser]

Neat, Daniel! How could I miss that one
I must be getting too old for this

Regards
LeoH