Excel Ranked Results

[LGMan]

Hi, I have a requirement to rank some results in Excel.
Basically I have Column A which contains an Area Code, Col.B holds salesmen and Col.C holds their sales figures Col.D shows the rank.
Currently Col.D is rank on all Col.C figures regardless of Area.
Is it possible to restart the rank when there is a change in the Area Code. Please note that the number of salesmen is not fixed so it's not a case of A1:A4 always being Area 1 and so on.

Hope this makes sense and thanks in advance for any advice.

 

[SkipVought]

Hi,

RANK is a function. Rank can also mean SORT in some order.

So please explain what you mean by rank som results.

Please post a cohesive example of the data and what you mean by rank with respect to your example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Here is an example, sorry if it doesn't line up
Sales Rank is a Function =RANK(C2,$C$2:$C$8,0)

Area Sales ID Sales Sales Rank
West ASC 785 4
West SHC 984 1
West JFP 824 3
Mids SLH 683 6
Mids EMS 716 5
South RGF 598 7
South LMG 853 2

What I'd like is for there to be a rank on each area

 

[AnotherHiggins]

The easiest thing that comes to mind is to go to Data > Sort, and sort by Area Code, then by Sales Descending.

I'm sure there is a formulaic answer, but nothing comes immediately to mind and I'm pretty sure it will be a complicated formula. If I have time, I'll play around with it, though.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

You could use the OFFSET function to define a range that is a subset of column C, based on the MATCH offset of the Area value and the COUNTIF of the Area Value...
[tt]
=RANK(C2,OFFSET(A1,MATCH(A2,$A$2:$A$8,0),2,COUNTIF($A$2:$A$8,A2),1),0)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks Guys.
Sorry for such a dumb question, when I try and copy Skips function into the cells beneath it changes from

=RANK(C2,OFFSET(A1,MATCH(A2,$A$2:$A$8,0),2,COUNTIF($A$2:$A$8,A2),1),0)

to

=RANK(C3,OFFSET(A2,MATCH(A3,$A$2:$A$8,0),3,COUNTIF($A$2:$A$8,A3),1),0)

Is there any way to copy the function keeping the reference to A1.

Thanks

 

[SkipVought]

I assume that you have a row of headings, per your example...
[tt]
D2: =RANK(C2,OFFSET($A$1,MATCH(A2,$A$2:$A$8,0),3,COUNTIF($A$2:$A$8,A3),1),0)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

:-( Sorry was having a real dumb moment. I have recently come back to a job where I use Excel and VBA after 5 years on another project. Thanks for bearing with me

 

[SkipVought]

No problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}