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

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

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

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

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