Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Function to Return Top 5 Values Only
I have a sheet with the following detail which I wish to Return the Top
5 Sales Locations only in Sheet2. My summary detail is as follows all located on Sheet 1 ColA = Location Name ColB = Sales Revenue ColC = Sales Ranking Number (1 = highest) On Sheet2 I wish to just see whatever locations are in the Top 5 with their respective Sales revenue Amounts, so eg London £10,750 Rank No 1 Paris £7,500 Rank No 2 etc until I show the Top 5 I have 35 locations in total on Sheet1 Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Function to Return Top 5 Values Only
On a separate sheet in A1 add
=INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$B:$B,ROW($A1 )),Sheet1!$B:$B,0)) and copy acroos to B and down 5 rows. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Sean" wrote in message ups.com... I have a sheet with the following detail which I wish to Return the Top 5 Sales Locations only in Sheet2. My summary detail is as follows all located on Sheet 1 ColA = Location Name ColB = Sales Revenue ColC = Sales Ranking Number (1 = highest) On Sheet2 I wish to just see whatever locations are in the Top 5 with their respective Sales revenue Amounts, so eg London £10,750 Rank No 1 Paris £7,500 Rank No 2 etc until I show the Top 5 I have 35 locations in total on Sheet1 Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Function to Return Top 5 Values Only
Source data assumed in Sheet1's A2:C36
with no duplicate rankings within C2:C36 In Sheet2, Put in say, A2: =INDEX(Sheet1!A$2:A$36,MATCH(SMALL(Sheet1!$C$2:$C$ 36,ROW(A1)),Sheet1!$C$2:$C$36,0)) Copy A2 to C2, fill down to C6 to return the top 5 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sean" wrote in message ups.com... I have a sheet with the following detail which I wish to Return the Top 5 Sales Locations only in Sheet2. My summary detail is as follows all located on Sheet 1 ColA = Location Name ColB = Sales Revenue ColC = Sales Ranking Number (1 = highest) On Sheet2 I wish to just see whatever locations are in the Top 5 with their respective Sales revenue Amounts, so eg London £10,750 Rank No 1 Paris £7,500 Rank No 2 etc until I show the Top 5 I have 35 locations in total on Sheet1 Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Function to Return Top 5 Values Only
Thanks Guys
Max wrote: Source data assumed in Sheet1's A2:C36 with no duplicate rankings within C2:C36 In Sheet2, Put in say, A2: =INDEX(Sheet1!A$2:A$36,MATCH(SMALL(Sheet1!$C$2:$C$ 36,ROW(A1)),Sheet1!$C$2:$C$36,0)) Copy A2 to C2, fill down to C6 to return the top 5 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sean" wrote in message ups.com... I have a sheet with the following detail which I wish to Return the Top 5 Sales Locations only in Sheet2. My summary detail is as follows all located on Sheet 1 ColA = Location Name ColB = Sales Revenue ColC = Sales Ranking Number (1 = highest) On Sheet2 I wish to just see whatever locations are in the Top 5 with their respective Sales revenue Amounts, so eg London £10,750 Rank No 1 Paris £7,500 Rank No 2 etc until I show the Top 5 I have 35 locations in total on Sheet1 Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Function to Return Top 5 Values Only
Welcome, Sean !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sean" wrote in message ups.com... Thanks Guys |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
Can a function return a Null (blank ) value? Maybe a custom functi | Excel Worksheet Functions | |||
return multiple corresponding values in excel | Excel Worksheet Functions |