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 a

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.

--
Biff
Microsoft Excel MVP


""Questor"" wrote in message
...
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?