Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
Hello,
What I am trying to do is get a ranking based off of data. Example: I have a database with store #'s and their sales. I want to be able to rank who did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a different sheet than what the data is kept in. Anyone got any ideas??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
Say your original data is in Sheet1 column A. In Sheet2, A1 enter:
=LARGE(Sheet1!A:A,ROW()) and copy down The highest value will be first, the second highest nextm etc. -- Gary's Student "Marcus" wrote: Hello, What I am trying to do is get a ranking based off of data. Example: I have a database with store #'s and their sales. I want to be able to rank who did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a different sheet than what the data is kept in. Anyone got any ideas??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
I also want the store # with it. Example. Sheet1 Column A is the store #,
Column 2 is the sales. I want to return both columns based of the highest sales. "Gary''s Student" wrote: Say your original data is in Sheet1 column A. In Sheet2, A1 enter: =LARGE(Sheet1!A:A,ROW()) and copy down The highest value will be first, the second highest nextm etc. -- Gary's Student "Marcus" wrote: Hello, What I am trying to do is get a ranking based off of data. Example: I have a database with store #'s and their sales. I want to be able to rank who did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a different sheet than what the data is kept in. Anyone got any ideas??? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
O.K., we modify the ranking formula because your original sales data is in
column B: =LARGE(Sheet1!B:B,ROW()) (still in column A of Sheet2) Now we have to do the equivalent of VLOOKUP to get the store number: =INDIRECT("Sheet1!A" & MATCH(A1,Sheet1!B:B,0)) (in another column of Sheet2) -- Gary's Student "Marcus" wrote: I also want the store # with it. Example. Sheet1 Column A is the store #, Column 2 is the sales. I want to return both columns based of the highest sales. "Gary''s Student" wrote: Say your original data is in Sheet1 column A. In Sheet2, A1 enter: =LARGE(Sheet1!A:A,ROW()) and copy down The highest value will be first, the second highest nextm etc. -- Gary's Student "Marcus" wrote: Hello, What I am trying to do is get a ranking based off of data. Example: I have a database with store #'s and their sales. I want to be able to rank who did the best in sales on a 1st, 2nd, 3rd, and so on, and return it to a different sheet than what the data is kept in. Anyone got any ideas??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
Hi Gary''s Student, surely INDEX is preferable to INDIRECT..... =INDEX(Sheet1!A:A,MATCH(A1,Sheet1!B:B,0)) although there will be problems with this approach if there are ties...... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=537046 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
You are correct, INDEX() works just fine. I only wish we could use VLOOKUP
here. -- Gary's Student "daddylonglegs" wrote: Hi Gary''s Student, surely INDEX is preferable to INDIRECT..... =INDEX(Sheet1!A:A,MATCH(A1,Sheet1!B:B,0)) although there will be problems with this approach if there are ties...... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=537046 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
What about overcoming the problem with tied values - if you have two
second placed stores then only the first in the list will be reported. The way I have overcome this in the past is to have a helper column which records the position of the item, then use this to determine the range to use for the next LARGE function (using INDIRECT - it gets messy). Pete |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking Data
The index worked great. Thank you.
"Pete_UK" wrote: What about overcoming the problem with tied values - if you have two second placed stores then only the first in the list will be reported. The way I have overcome this in the past is to have a helper column which records the position of the item, then use this to determine the range to use for the next LARGE function (using INDIRECT - it gets messy). Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
Importing Data | Excel Worksheet Functions | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |