Rankings

[Harquor]

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]

 

[yee388]

I used a few extra columns... your solution is on the far right.

Book1
	A	B	C	D	E	F	G	H	I
1		Widgets	Gizmos	Gadgets	Average	Rank		RankedNames	Avg%
2	Bob	18.68%	14.67%	12.57%	15.31%	3		Kay	18.14%
3	Jim	12.07%	11.64%	7.61%	10.44%	8		Dan	15.54%
4	Mac	11.52%	8.83%	9.10%	9.82%	10		Bob	15.31%
5	Tim	18.20%	5.42%	8.16%	10.59%	7		Ron	13.92%
6	Dan	16.03%	14.20%	16.39%	15.54%	2		Kim	10.97%
7	Sam	11.08%	7.17%	9.54%	9.26%	12		Jon	10.89%
8	Jon	18.26%	8.30%	6.10%	10.89%	6		Tim	10.59%
9	Jan	13.11%	7.20%	10.54%	10.28%	9		Jim	10.44%
10	Tom	12.46%	2.33%	13.80%	9.53%	11		Jan	10.28%
11	Don	11.93%	5.92%	7.79%	8.55%	13		Mac	9.82%
12	Kim	14.68%	9.91%	8.33%	10.97%	5		Tom	9.53%
13	Ron	17.70%	8.53%	15.54%	13.92%	4		Sam	9.26%
14	Kay	22.32%	14.71%	17.39%	18.14%	1		Don	8.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]

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]

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]

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!