Capturing Top 5 Performers

[Questor]

I'm using the LARGE function to find the top five values (number) in column
"F" of a large list and place those values in Cells N1 thru N5 — Cell
N1=LARGE(F1:F100;1) thru Cell N5=LARGE(F1:F100;5).
Having found these values I want to associate them with the related text in
column "A" of that list so that I can correlate the sales figures with the
related salesmen (from column A) in cells M1 to M5. I used the LOOKUP
function to do that — M1=LOOKUP(N1;F1:F100;A1:A100) thru
M5=LOOKUP(N5;F1:F100;A1:A100).

I have two problems:
First - two salesmen have the same sales figures and the LARGE function
seems to arbitrarily distinguish them as the #1 and #2 top salesmen (although
I'm not really sure of that)
Second - the LOOKUP function is not returning expected results with two
variations
(a) cells M1 and M2 do not distinguish between top salesmen #1 and #2 and
returns #1 in both cases
(b) some returns from the list of salesmen do not correlate to their sales
totals

Can anyone help me sort this out?

 

[Ron Rosenfeld]

I'm using the LARGE function to find the top five values (number) in column
"F" of a large list and place those values in Cells N1 thru N5 — Cell
N1=LARGE(F1:F100;1) thru Cell N5=LARGE(F1:F100;5).
Having found these values I want to associate them with the related text in
column "A" of that list so that I can correlate the sales figures with the
related salesmen (from column A) in cells M1 to M5. I used the LOOKUP
function to do that — M1=LOOKUP(N1;F1:F100;A1:A100) thru
M5=LOOKUP(N5;F1:F100;A1:A100).

I have two problems:
First - two salesmen have the same sales figures and the LARGE function
seems to arbitrarily distinguish them as the #1 and #2 top salesmen (although
I'm not really sure of that)
Second - the LOOKUP function is not returning expected results with two
variations
(a) cells M1 and M2 do not distinguish between top salesmen #1 and #2 and
returns #1 in both cases
(b) some returns from the list of salesmen do not correlate to their sales
totals

Can anyone help me sort this out?

Chip Pearson has a page discussing (with solutions) the problems of generating
unique rankings and returning the associated data when dealing with this sort
of issue. See: http://www.cpearson.com/excel/rank.aspx
--ron

 

[Questor]

Thanks Ron, that helps. I'm still having trouble understanding why the
LOOKUP function is returning unexpected results. You can contact me at
(e-mail address removed) to arrange to obtain a simple file that demonstrates the
problem if you wish.
Again, thanks

 

[Ron Rosenfeld]

Thanks Ron, that helps. I'm still having trouble understanding why the
LOOKUP function is returning unexpected results. You can contact me at
(e-mail address removed) to arrange to obtain a simple file that demonstrates the
problem if you wish.
Again, thanks

I'll guess that either your data is not what you think it is (text vs numbers)
or you have not read HELP for the LOOKUP function? In particular,

" Important: The values in lookup_vector must be placed in ascending order:
.... otherwise, LOOKUP might not return the correct value. Uppercase and
lowercase text are equivalent."

You've mentioned nothing about having sorted your data.

I would suggest that you append to your data a column with the unique rankings
for the salespeople, generated as per Chip's web site; and then use something
like (not tested)

=INDEX($A$1:$A$100,MATCH(N1,$G$1:$G$100,0))

assuming column G contains your rankings; column A contains your sales people
names, and N1 contains the rank you are looking for.
--ron

 

[T. Valko]

Top/Bottom n lists are more complicated than people realize if there are
ties involved.

The top 5 might be more than 5 if the ties occur within the top 5 ranked
values. For example:

10,9,9,9,8,8,8,8,7,6,5,4,3,2,1

In that list there are a total of 8 that fall within the top 5. They are:

10,9,9,9,8,8,8,8

Here's a small sample file that demonstrates this:

xTop5.xls 21kb

http://cjoint.com/?eumMee1zTi

Column A = names
Column B = sales amounts
Column C = formula that creates a unique rank based on sales amount
E2 = formula that returns the count of how many sales amounts rank within
the top 5
Column F = array formula** that returns the names in descending order that
correspond to the top 5 sales amounts

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Chip Pearson]

If there might be duplicates in the sales figures, you'll need to
create unique ranks. If you don't use unique ranks, several items will
have the same rank value and rank values will be skipped. You might
end up with ranks like, say, 1,2,2,2,5,6,7,7,7,10.. Clearly, this is
no good. This is why you need unique ranks.

For example, suppose you have salesman names in A1:A10 and sales
amounts in B1:B10. Insert a new column C and enter

=RANK(B1,B$1:B$10,0)+COUNTIF(B$1:B1,B1)-1

and fill down to B10. Make sure you use the $ characters in the
formula as shown. Otherwise, it won't work.

This will create unique ranks for the items in B1:B10. If there is a
tie, the rank is based on the position of the sales figures appear in
the list. For example, if both salesman X and Y have figures of 1000,
X will be ranked above Y if X's sales amount is in a lower numbered
row than Y's. There are ways to use subordinate tables to break ties,
but you don't seem to have such data in your model.

Once you have the unique ranks in C1:C10, enter the following formula
in D1 and fill down to D10:

=OFFSET(A$1,MATCH(ROW()-ROW(D$1)+1,C$1:C$10,0)-1,0,1,1)

This will return the names of the salesman in the order specified by
the ranks in C1:C10.

See www.cpearson.com/Excel/Rank.aspx for more details about ranking
data, including tie breaking.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)