A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Capturing Top 5 Performers



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2009, 05:42 PM posted to microsoft.public.excel.worksheet.functions
questor
external usenet poster
 
Posts: 17
Default Capturing Top 5 Performers

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?
  #2  
Old April 18th, 2009, 07:24 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Capturing Top 5 Performers

On Sat, 18 Apr 2009 09:42:02 -0700, "Questor"
wrote:

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
  #3  
Old April 18th, 2009, 07:54 PM posted to microsoft.public.excel.worksheet.functions
questor
external usenet poster
 
Posts: 17
Default Capturing Top 5 Performers

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

"Ron Rosenfeld" wrote:

On Sat, 18 Apr 2009 09:42:02 -0700, "Questor"
wrote:

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

  #4  
Old April 18th, 2009, 09:17 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Capturing Top 5 Performers

On Sat, 18 Apr 2009 11:54:06 -0700, "Questor"
wrote:

Thanks Ron, that helps. I'm still having trouble understanding why the
LOOKUP function is returning unexpected results. You can contact me at
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
  #5  
Old April 18th, 2009, 10:07 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Capturing Top 5 Performers

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?



  #6  
Old April 18th, 2009, 11:33 PM posted to microsoft.public.excel.worksheet.functions
Chip Pearson
external usenet poster
 
Posts: 1,343
Default Capturing Top 5 Performers

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)


On Sat, 18 Apr 2009 09:42:02 -0700, "Questor"
wrote:

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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.