Rankings

Harquor

New Member
Joined
Jun 22, 2006
Messages
36
Is there a way to write a formula to rank a people based on data in multiple areas? I have 13 sales reps and want to see who is the best overall. The cells with their conversion percents are an average imported from another worksheet by using ='Widgets'!B4 for example. These averages will be changing constantly so ranks should vary from week to week. I would like to see the names and data shift from top to bottom (best to worst) as their rank changes. Below is an example of what the sheet would look like so far. Help is VERY much appreciated as I'm not so saavy with Excel! Thank you!

I tried to copy this but it didn't work very well. A, B and C are the columns and the rows are numbered on the left.

A B C D
1 Widgets Gizmos Gadgets
2 Bob 18.68% 14.67% 12.57%
3 Jim 12.07% 11.64% 7.61%
4 Mac 11.52% 8.83% 9.10%
5 Tim 18.20% 5.42% 8.16%
6 Dan 16.03% 14.20% 16.39%
7 Sam 11.08% 7.17% 9.54%
8 Jon 18.26% 8.30% 6.10%
9 Jan 13.11% 7.20% 10.54%
10 Tom 12.46% 2.33% 13.80%
11 Don 11.93% 5.92% 7.79%
12 Kim 14.68% 9.91% 8.33%
13 Ron 17.70% 8.53% 15.54%
14 Kay 22.32% 14.71% 17.39%
[/img]
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
I used a few extra columns... your solution is on the far right.
Book1
ABCDEFGHI
1WidgetsGizmosGadgetsAverageRankRankedNamesAvg%
2Bob18.68%14.67%12.57%15.31%3Kay18.14%
3Jim12.07%11.64%7.61%10.44%8Dan15.54%
4Mac11.52%8.83%9.10%9.82%10Bob15.31%
5Tim18.20%5.42%8.16%10.59%7Ron13.92%
6Dan16.03%14.20%16.39%15.54%2Kim10.97%
7Sam11.08%7.17%9.54%9.26%12Jon10.89%
8Jon18.26%8.30%6.10%10.89%6Tim10.59%
9Jan13.11%7.20%10.54%10.28%9Jim10.44%
10Tom12.46%2.33%13.80%9.53%11Jan10.28%
11Don11.93%5.92%7.79%8.55%13Mac9.82%
12Kim14.68%9.91%8.33%10.97%5Tom9.53%
13Ron17.70%8.53%15.54%13.92%4Sam9.26%
14Kay22.32%14.71%17.39%18.14%1Don8.55%
Sheet1


In E2: =AVERAGE(B2:D2)
In F2: =RANK(E2,E$2:E$14)
In H2: =INDEX(A$2:F$14,MATCH(ROW()-1,F$2:F$14,0),1)
In I2: =INDEX(A$2:F$14,MATCH(H2,A$2:A$14,0),5)

All formulas copied down.

Hope it helps!
 

Harquor

New Member
Joined
Jun 22, 2006
Messages
36
This is FABULOUS! Thank you very much! I have one other question however. Some of the other data that I am using is formatted differently.

I have 13 different columns that I use and they have cells formatted in order from column B to N. They are: percent, percent, percent, percent, percent, number, custom 0_), percent, percent, currency, currency, currency, currency. How can they be ranked with all those different formats?

Sorry, I should have added this to my initial post.
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
I'm not sure what you mean... my column E is the average of the 3 % figures, and that's what I used to rank them.

If you have 13 inputs to their "score", I have no way of knowing how you want to weight/measure each of the 13 factors into a single score for each person.

One thought would be to give each one a rank in each of the 13 factors, add up the ranks, and the lowest one is the top sales rep. However, there are millions of ways to get a cumulative score based on 13 inputs... you'll have to decide the process on your own. I can only help with making it work in Excel.
 

Harquor

New Member
Joined
Jun 22, 2006
Messages
36
Ahh, I like your idea of assigning each stat a rank and then going with lowest score. I'll give that a shot. Thank you again for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,708
Members
410,630
Latest member
Maggie28
Top