how do i rank based on other rankings?

[djeans]

this is hard to describe, what i need to do is rank a sales team base
on six different criteria, and then rank them based on their ranking
in each of those.

For instance, I would like to rank each person on sales of items 1-6
and then rank their performance overall to see who is the top performe
over all six combined

I have tried using pivot tables, without much luck, and have trie
using RANK, but without much success.

I have a worksheet that I use to enter the info for each person, fo
example
A1=Name
B1-B6 = Tracked Items (Total Sales, PPA, etc)
C1-I1 = Monday - Sunday
J1 = totals

It starts over again at A7 with the next sales persons name

I have another sheet that I use to sort each category, which has worke
well in that respect, but haven't been able to rank each perso
overall.

I have attached the file if anyone would like to look at it and offe
any suggestions on how i could do this.

Thanks in advance.

Darron

ps

I tried to attach the file, but it was too big to post. I will email i
to anyone who would care to take a look at it

 

[Debra Dalgleish]

You can use the SUMIF function to calculate each person's total score,
then rank those totals. For example, with a list of names, starting in
cell L2:
=SUMIF($A$2:$A$19,L2,$J$2:$J$19)
In another column, rank the totals:
=RANK(M2,$M$2:$M$19)

 

[djeans]

I left out one important detail.

Some of the categories are based on dollar amounts, and some are base
on number sold.

For instance, item one may be total sales $, and item 2 may be # of
specific item sold.

Not sure, but I think that using the method you suggested would no
work accurately in that instance.

Darro

 

[Debra Dalgleish]

See this post by Bernie Deitrick, for a user defined formula to
calculate rank for specific criteria within a list:


http://groups.google.com/groups?&selm=#[email protected]

Use it to rank the items in the main list, with TRUE as the fifth argument.

In an adjacent column, calculate the total ranking score for each person:
=IF(COUNTIF($A$1:$A2,A2)=1,SUMIF($A$2:$A$100,A2,$K$2:$K$100),0)
where the item ranks are in column K

In the next column, calculate the overall rank:

=IF(L2=0,"",RANK(L2,$L$2:$L$100))

 

[djeans]

I've wrestled with this for a couple of days now, but Im not gettin
it.

Cell A1 is the salesmans name

Cell B1:B6 are the categories that are being ranked for each person.

Columns C:I are Monday-Sunday.

Column J is the sum for each category.

Cell A7 contains the next salesmans name, with B7:B12 are each categor
again.

and so on and so forth for about 25 sales people.

I would like to rank each server on each category. So, I need to ran
Cells J1 against J7 against J13, etc. Then rank J2 against J8 etc. .


Then rank them again based on the rankings they received on eac
category.

Is that possible given the formulas that you suggested?

thanks again.

darro

 

[djeans]

A can't get that formula to return anything other than #name?

=RankIf(Z1,$Z$1:$Z$100,$A$1:$A$100,A1, True)

I must be doing something wrong.

darro

 

[AlfD]

Darron:

I haven't got past first bas, yet.

The first set of rankings (for each of the six areas of endeavour
don't worry me too much , though it would be a lot easier if th
various items for ranking were one to a column, not piled in groups i
the same column.

It's the second bit : ranking the rankings. This doesn't look like a
easy thing to do unless you have some defined way of weighting the
first-stage rankings. E.g. is a ranking of 1 on criterion A more o
less important in the final ranking than a ranking of 1 on criterion B
The discussion so far seems to have taken it as a "given" that all ar
of equal importance. Is that the rule?

Al

 

[Debra Dalgleish]

What's in column Z? Your previous posts only mentioned data in columns A:J.

Where did you store the code for the RankIf formula?

 

[djeans]

Each ranking would be of equal importance for this. No one categor
would be weighted heavier than any other category.

Debra,

I copied and pasted the formula from the page you referenced to sho
the formula.

In my worksheet, i tried changing the z's to a's etc, and every othe
possible combination, but never got anything other than #name

 

[Debra Dalgleish]

The link also contains a user defined function (RankIf), which is code
that you paste into your workbook.

Copy the code from Bernie's posting, then switch to Excel
Press Alt+F11, to open the Visual Basic Editor
Choose Insert>Module
Paste the code
Choose File>Close and Return to Microsoft Excel.

 

[djeans]

That would explain why i wasn't getting anywhere LOL.

I will try that when i get home.

I tried resorting the data into just three columns, thinking that mayb
that would help make things easier.

Column B has names
Column C has categories (per person average, sales $, etc.)
Column D has $ dollar amounts etc.

ie

A B C
Dave PPA 24.95
Dave Retail 325.50
John PPA 21.95

and so on.

Will this formula help me with the ranking in that respect?

Thanks


Darro