Ranking or sorting function

[craig]

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

 

[T. Valko]

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

 

[Mike H]

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]

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

 

[Max]

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(SMALL($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".