using the large function, row and column function together

[pscseti]

Hi,

I have a column of numbers on which I'm using the large function. I basically need the top ten numbers in the column. Then for each of the top 10 numbers (say sales numbers), I'm trying to get excel to show the sales persons name, but the name is to the left of the row with sales number. So I am trying to figure out a way to have excel give me the row # in which the largest value resides. Then I plan to use the offset function from the row & the column to return the salespersons name. Any suggestions?

Tx,

 

[agihcam]

Hi -
is autofilter method > top ten didn't work for you?

 

[Aladin Akyurek]

Re: using the large function, row and column function togeth

Hi,

I have a column of numbers on which I'm using the large function. I basically need the top ten numbers in the column. Then for each of the top 10 numbers (say sales numbers), I'm trying to get excel to show the sales persons name, but the name is to the left of the row with sales number. So I am trying to figure out a way to have excel give me the row # in which the largest value resides. Then I plan to use the offset function from the row & the column to return the salespersons name. Any suggestions?

Tx,

Top N List.xls
	A	B	C	D	E	F	G	H	I
1					3
2					4
3	Sales Force	Sales			Top Performer(s)	Top Sale(s)		Sum of Sales
4	dawn	75			brian	90		Sales Force	Total
5	damon	85			jon	90		jon	90
6	dan	70			damon	85		brian	90
7	brian	90			christine	85		christine	85
8	christine	85						damon	85
9	ian	80
10	jon	90
11	fred	75
12	albert	80
13	david	84
Data

E1: 3

Specifies the desired size of Top N list. Change this to 10 for your actual data.

E2:

=COUNTIF(B4:B13,">="&LARGE(B4:B13,E1))

E4:

=IF(N(F4),INDEX(A$4:A$13,SMALL(IF(B$4:B$13=F4,ROW(B$4:B$13)-ROW(B$4)+1),COUNTIF(F$4:F4,F4))),"")

which is confirmed with control+shift+enter (not with just enter) then copied down.

F4:

=IF(ROWS(F$4:F4)<=$E$2,LARGE($B$4:$B$13,ROWS(F$4:F4)),"")

The same results can also be obtained by constructing a pivot table as shown in H3:I8.