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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|