Rank Question

[dchristo]

I need to figure out how to rank by manager ex:

Processor Manager Score Rank
Albert Scott 80.9 2
Brian Scott 91.5 1
Cathy Scott 79.3 3
Mable Delores 99.9 1
Elaine Delores 95.6 2
Tony Delores 73.5 3

I have 100 or so managers so I want a formula to calculate it automatically
without any manual intervention.

Thanks

 

[trip_to_tokyo]

In EXCEL 2007, assuming your data is in range A 1 to D 7 inclusive.

1. Gives cells C 2 to C 7 inclusive a Range Name of, for example, RangeName.

These 6 cells contain your Score column.

2. In cell E 2 type the following:-

=RANK(C2,RankRange)

- and copy the above down and including cell E 3 to and including cell E 7.

3. Your automatic rankings will now be in the E column.

The highest score will be ranked first (99.9) and the lowest score will be
ranked 6th (73.5).

Please hit Yes if my comments have helped.

Thanks.

 

[dchristo]

I need it to rank by Manager, the manager could change (see the example
under the Rank column)- and I am using Excel 2003

 

[Luke M]

Assuming Manager name is in column B and are grouped together, score in
column C...
In D2
=RANK(C2,OFFSET(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$B2,Sheet1!$B:$B,0)),,,COUNTIF(Sheet1!$B:$B,Sheet1!$B2)))

 

[dchristo]

This is perfect!!!! Thank you very much.

Assuming Manager name is in column B and are grouped together, score in
column C...
In D2:
=RANK(C2,OFFSET(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$B2,Sheet1!$B:$B,0)),,,COUNTIF(Sheet1!$B:$B,Sheet1!$B2)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[מיכ×ל (מיקי) ×בידן]

Chapeau !!!
Micky

Assuming Manager name is in column B and are grouped together, score in
column C...
In D2:
=RANK(C2,OFFSET(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$B2,Sheet1!$B:$B,0)),,,COUNTIF(Sheet1!$B:$B,Sheet1!$B2)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[T. Valko]

Try this...

Entered in D2 and copied down as needed.

=SUMPRODUCT(--(B$2:B$7=B2),--(C2<C$2:C$7))+1