# Rankings

#### Harquor

##### New Member
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
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]

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### yee388

##### Well-known Member
I used a few extra columns... your solution is on the far right.
Book1
ABCDEFGHI
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
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
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
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!

Replies
11
Views
1K
Replies
5
Views
566
Replies
10
Views
2K

1,181,963
Messages
5,932,986
Members
436,871
Latest member
Nickytoo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back