Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking or sorting function
I am looking for a way to have some information ranked in ascending order. I
have a list of names with test scores. I need to rank each name based on their test score. I am having trouble when there are test scores the same. This is throwing off the ranking as the scores are the same number. Does anyone have a function I can use for ranking when the numbers are the same. All persons must show on my results and their test results. Thanks, Craig |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking or sorting function
I don't understand why this is a problem. Maybe you're leaving out a bit of
info? If you're using a RANK formula, every score will receive a rank number and then you can sort by that rank number. Biff "craig" wrote in message ... I am looking for a way to have some information ranked in ascending order. I have a list of names with test scores. I need to rank each name based on their test score. I am having trouble when there are test scores the same. This is throwing off the ranking as the scores are the same number. Does anyone have a function I can use for ranking when the numbers are the same. All persons must show on my results and their test results. Thanks, Craig |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking or sorting function
Craig,
With the test scores in Column B1 down type this in C1 and drag down as required. =RANK(B1,B$1:B$50) If 2 scores have an equal rank then they will both get the same rank For example if there is a joint No1 they will both get 1 and there will be no No2. Mike "craig" wrote: I am looking for a way to have some information ranked in ascending order. I have a list of names with test scores. I need to rank each name based on their test score. I am having trouble when there are test scores the same. This is throwing off the ranking as the scores are the same number. Does anyone have a function I can use for ranking when the numbers are the same. All persons must show on my results and their test results. Thanks, Craig |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking or sorting function
Thank you for your replies! The issue is, I am using one sheet for entering
the data and another sheet to summarize all of the people with their test scores. This is because the data sheet has way to much information. On the results sheet I want to be able to have the scores sorted ascending automatically and then be able to look up who each of the scores belong to. I attempted to rank each score on the datasheet and then do a vlookup off of each rank to bring over to the results sheet the score and the name of the person but that doesn't work when their are similar test scores which have the same rank. Below is an example I get the information from the data sheet and it is summarize in the summary sheet. (The rank numbering 1,2,3 etc is typed in on the summary sheet and the data sheet is the rank formula. I then use the rank number on the summary sheet to do a vlookup for the remainer info on the data sheet. Summary Sheet Rank Name Test Score 1 Jennifer 75 2 Matt 80 3 N/a N/A 4 Scott 90 Data sheet Rank 2 Mike 80 4 Scott 90 1 Jennifer 75 2 Matt 80 Thanks Craig "Mike H" wrote: Craig, With the test scores in Column B1 down type this in C1 and drag down as required. =RANK(B1,B$1:B$50) If 2 scores have an equal rank then they will both get the same rank For example if there is a joint No1 they will both get 1 and there will be no No2. Mike "craig" wrote: I am looking for a way to have some information ranked in ascending order. I have a list of names with test scores. I need to rank each name based on their test score. I am having trouble when there are test scores the same. This is throwing off the ranking as the scores are the same number. Does anyone have a function I can use for ranking when the numbers are the same. All persons must show on my results and their test results. Thanks, Craig |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking or sorting function
One way to achieve it using non-array formulas ..
Assume the source names and scores are in sheet: Data, in cols A and B, from row2 down In sheet: Summary, Put in A2: =IF(Data!B2="","",Data!B2+ROW()/10^10) Leave A1 blank Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX(Data!A:A,MATCH(S MALL($A:$A,ROW(A1)),$A:$A,0))) Copy B2 to C2. Select A2:C2, copy down to cover the max expected extent of data in "Data", say down to C100. Hide away col A. Cols B & C will return the required full ascending sort of names and scores, sorted by scores, with lines returned neatly bunched at the top. Names with tied scores, if any, will be reflected in the same relative order that they appear within "Data". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "craig" wrote: Thank you for your replies! The issue is, I am using one sheet for entering the data and another sheet to summarize all of the people with their test scores. This is because the data sheet has way to much information. On the results sheet I want to be able to have the scores sorted ascending automatically and then be able to look up who each of the scores belong to. I attempted to rank each score on the datasheet and then do a vlookup off of each rank to bring over to the results sheet the score and the name of the person but that doesn't work when their are similar test scores which have the same rank. Below is an example I get the information from the data sheet and it is summarize in the summary sheet. (The rank numbering 1,2,3 etc is typed in on the summary sheet and the data sheet is the rank formula. I then use the rank number on the summary sheet to do a vlookup for the remainer info on the data sheet. Summary Sheet Rank Name Test Score 1 Jennifer 75 2 Matt 80 3 N/a N/A 4 Scott 90 Data sheet Rank 2 Mike 80 4 Scott 90 1 Jennifer 75 2 Matt 80 Thanks Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking Function | Excel Worksheet Functions | |||
sorting and ranking | Excel Discussion (Misc queries) | |||
need function to sum top ranking items in list | Excel Worksheet Functions | |||
ranking or sorting | Excel Worksheet Functions | |||
Ranking/Sorting more than 3 row's | Excel Discussion (Misc queries) |