using the large function, row and column function together

pscseti

Board Regular
Joined
Nov 26, 2002
Messages
67
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,
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
ABCDEFGHI
13
24
3Sales ForceSalesTop Performer(s)Top Sale(s)Sum of Sales
4dawn75brian90Sales ForceTotal
5damon85jon90jon90
6dan70damon85brian90
7brian90christine85christine85
8christine85  damon85
9ian80
10jon90
11fred75
12albert80
13david84
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,373
Members
410,911
Latest member
AniEx
Top