Ranking

[clarkebball22]

I have the table below. I would like to automatically rank these in the next columns (F-H). The first column is automatically ranked (already know the formula). Second column is names, 3rd column is companies sold, 4th column is internal information and the 5th column will be total employees at each of the companies. What is the formula to automatically put them in order from most companies sold (tie breaker of total employees)?

Ranks	Names	Companies	Internal	Employees
3	Name 1	12		120
5	Name 2	8		80
7	Name 3	4		40
1	Name 4	15		150
2	Name 5	12		124
4	Name 6	9		90
6	Name 7	6		60

<tbody>
</tbody>

 

[bbott]

The reason I am seeing something different is the ranking, I am seeing a N/A in the 3rd spot because both of the 2nd places are getting a 0.5 added. Thus both becoming 2.5 and screwing up the official rank.

You won't get a 2.5 with this formula:

=VLOOKUP(ROW(A1),$A$2:$C$8,COLUMN(A1),0)

 

[clarkebball22]

You won't get a 2.5 with this formula:

=VLOOKUP(ROW(A1),$A$2:$C$8,COLUMN(A1),0)

I don't get the 2.5's but I get a N/A for the tie. The first 2nd place shows up, but the second shows as a N/A.

 

[bbott]

I don't get the 2.5's but I get a N/A for the tie. The first 2nd place shows up, but the second shows as a N/A.

Your original table doesn't have any ties in column A, just 1-7. Is that not the case anymore?

 

[clarkebball22]

Correct, sorry I had just added the numbers myself. I need to rank them as well.

 

[clarkebball22]

Your original table doesn't have any ties in column A, just 1-7. Is that not the case anymore?

Correct, sorry I had just added the numbers myself. I need to rank them as well.

 

[bbott]

I have the table below. I would like to automatically rank these in the next columns (F-H). The first column is automatically ranked (already know the formula). Second column is names, 3rd column is companies sold, 4th column is internal information and the 5th column will be total employees at each of the companies. What is the formula to automatically put them in order from most companies sold (tie breaker of total employees)?

Ranks	Names	Companies	Internal	Employees
3	Name 1	12		120
5	Name 2	8		80
7	Name 3	4		40
1	Name 4	15		150
2	Name 5	12		124
4	Name 6	9		90
6	Name 7	6		60

<tbody>
</tbody>

You're first post says you already have the formula in column A. Do you need that formula or do you already have it?

 

[clarkebball22]

You're first post says you already have the formula in column A. Do you need that formula or do you already have it?

I had a formula, but then realized it didn't work for any ties. So, unfortunately, I don't have the correct formula to deal with any ties. All ties need to be broken down by Employees

 

[bbott]

I had a formula, but then realized it didn't work for any ties. So, unfortunately, I don't have the correct formula to deal with any ties. All ties need to be broken down by Employees

Check out this thread and see if it helps.

http://www.mrexcel.com/forum/excel-questions/293980-ranking-two-columns-data.html

Adapted to your data, the formula in A2 would be:

=COUNTIF($C$2:$C$8,"<"&C2)+SUMPRODUCT(--($C$2:$C$8=C2),--($E$2:$E$8 < E2))+1

 

[clarkebball22]

Check out this thread and see if it helps.

http://www.mrexcel.com/forum/excel-questions/293980-ranking-two-columns-data.html

Adapted to your data, the formula in A2 would be:

=COUNTIF($C$2:$C$8,"<"&C2)+SUMPRODUCT(--($C$2:$C$8=C2),--($E$2:$E$8 < E2))+1

Well, it works, except it's the opposite, haha. Thanks for all your help

 

[bbott]

Well, it works, except it's the opposite, haha. Thanks for all your help

Oops. Just change the < to >.

=COUNTIF($C$2:$C$8,">"&C2)+SUMPRODUCT(--($C$2:$C$8=C2),--($E$2:$E$8 < E2))+1