Hi,

Here is the run down:

Col A = Site Number - There are 419 sites scattered along the west coast ...
each with site a unique site number.
Col B = Manager's name - 7 managers to overlook the 419 sites.
Col C = Overall Score - Each site is given a score based upon a number of
various criteras.
Col D = Where I want the overall rank.

Problem: I want to rank Overall Score (Column C) by each individual manager
(Column B) and NOT site (Column A). So for example, if managers; Dave, Tom
and Larry have respective high scores within their own site numbers/territory
of 100.00 (Dave), 90.00 (Tom) and 85.00 (Larry) and respective low scores of
5.00 (Dave), 4.00 (Tom), 4.00 Larry) ... then I want the high and low numbers
all to be ranked within each manager bucket - so - Dave is ranked within his
own sites and not affected by the other manager site scores. I hope this
makes sense? I was hoping someone could provide a formula for this.

I used the formula below and it seems to have worked but ... for some reason
later on in the spreadsheet like around row 56 and on ... even if Column C
has a score ... Column D still returns a "#N/A" and I can't figure out why.

=IF(NOT(ISNUMBER(CZ6)),"N/A",RANK(CZ6,OFFSET(CZ$5,MATCH($C6,$C$6:$C$65536,0) ,0,COUNTIF($C:$C,$C6)),FALSE))

Mike

I know why this formula failed ... because I didn't have manager names sorted
ascending. So the question is, can I rank off of manager with it not being
sorted?

=IF(NOT(ISNUMBER(CZ6)),"N/A",RANK(CZ6,OFFSET(CZ$5,MATCH($C6,$C$6:$C$65536,0) ,0,COUNTIF($C:$C,$C6)),FALSE))


"inspirz" wrote:

Hi,

Here is the run down:

Col A = Site Number - There are 419 sites scattered along the west coast ...
each with site a unique site number.
Col B = Manager's name - 7 managers to overlook the 419 sites.
Col C = Overall Score - Each site is given a score based upon a number of
various criteras.
Col D = Where I want the overall rank.

Problem: I want to rank Overall Score (Column C) by each individual manager
(Column B) and NOT site (Column A). So for example, if managers; Dave, Tom
and Larry have respective high scores within their own site numbers/territory
of 100.00 (Dave), 90.00 (Tom) and 85.00 (Larry) and respective low scores of
5.00 (Dave), 4.00 (Tom), 4.00 Larry) ... then I want the high and low numbers
all to be ranked within each manager bucket - so - Dave is ranked within his
own sites and not affected by the other manager site scores. I hope this
makes sense? I was hoping someone could provide a formula for this.

I used the formula below and it seems to have worked but ... for some reason
later on in the spreadsheet like around row 56 and on ... even if Column C
has a score ... Column D still returns a "#N/A" and I can't figure out why.

=IF(NOT(ISNUMBER(CZ6)),"N/A",RANK(CZ6,OFFSET(CZ$5,MATCH($C6,$C$6:$C$65536,0) ,0,COUNTIF($C:$C,$C6)),FALSE))

Mike