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,
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,909
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top