Ranking on several fields

[Michael Chappell]

I have a spreadsheet that currently uses RANK to work out the best
performing salesperson in a team of 12.

Column E ranks column D (sales of product 1)
Column G ranks Column F (sales of product 2)
Column I ranks column H (sales of product 3)
Column K ranks column J (sales of product 4)
Column M ranks Column L (sales of product 5)


In column N I have totalled all the ranking values in E,G,I,K,M. This will
often result in duplicate values, which causes big problems in column O,
which is ranking column N.

I need column O to be able to break the tie when it occurs, firstly using
column D values, but if this still results in a tie, then referring to
column F values (the bigger taking the lead). This should avoid the ties in
column O and would be very rare to still result in a tie.

Any ideas?

Thanks

 

[Guest]

consider sorting rather than ranking

 

[T. Valko]

I'm assuming you have 12 rows of data, say, D2:O13, and you're ranking
highest to lowest. By summing the individual ranks and then ranking that sum
you are in effect reversing the the rank order from lowest to highest.

Enter this formula in Q2 and copy down to Q13:

=SUM(D2,F2,H2,J2,L2)

Enter this formula in P2 and copy down to P13:

=RANK(Q2,Q$2:Q$13,1)

Compare the ranks in column P to the ranks you have in column O. You'll
notice that they are the same with differences for any ties that might have
been in column O.

Biff

 

[Michael Chappell]

I'm assuming you have 12 rows of data, say, D2:O13, and you're ranking
highest to lowest. By summing the individual ranks and then ranking that
sum you are in effect reversing the the rank order from lowest to
highest.

Enter this formula in Q2 and copy down to Q13:

Enter this formula in P2 and copy down to P13:

Compare the ranks in column P to the ranks you have in column O. You'll
notice that they are the same with differences for any ties that might
have been in column O.

Biff

Thanks Biff,

That appears to be the kind of thing I'm looking for. Unfortunately, the
reason I've ranked individually is that columns D and F have to show the
actual *number* of items sold and columns H,J and L show the *monetary
values* of items sold. Is there another way round it?

If it's any help, I have put up the spreadsheet at:

http://tinyurl.com/2r2fjf

Michael

 

[T. Valko]

That file really doesn't help. There's no raw data. What about that UDF?

Biff

 

[Michael Chappell]

That file really doesn't help. There's no raw data. What about that UDF?

Biff

UDF?

Michael

 

[T. Valko]

UDF?

User Defined Function

The file looks like a demonstration file for a custom UDF that calculates
ranks.

Biff

 

[Rupert]

So say you have 3 ranks, rank1 the most important, rank2 & rank3 to
break ties

How about looking at:
Rank = rank1 + rank2/1000 + rank3/1000000

(Assuming you've got <1000 sales guys...)