As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
Excel Formula Help - RANK formula
Alpine
Registered User regular
Registered User regular
My mom needs a specific excel formula to help her tabulate scores for a gymnastics meet she's organizing next weekend.
Here's the problem she needs to solve: She has a list of scores that need a Lookup code to assign an Ordinal ranking to them (1st, 2nd, 3rd etc), but it needs to recognize tied scores, so that if she has values
8.4
8.2
8.2
8.1
7.9
It will rank them as 1 2 2 4 5, rather than 1 2 2 3 4.
The way she has it set up, an IF statement won't work easily enough to be feasible. It needs to be some sort of RANK that can deal with the Total Scores as they are elsewhere in the spreadsheet.
Here's the problem she needs to solve: She has a list of scores that need a Lookup code to assign an Ordinal ranking to them (1st, 2nd, 3rd etc), but it needs to recognize tied scores, so that if she has values
8.4
8.2
8.2
8.1
7.9
It will rank them as 1 2 2 4 5, rather than 1 2 2 3 4.
The way she has it set up, an IF statement won't work easily enough to be feasible. It needs to be some sort of RANK that can deal with the Total Scores as they are elsewhere in the spreadsheet.
Alpine on
0
Posts
Can you sort the results first so that they're from highest to lowest?
If so, you can do something quick and dirty like shown here.
Edit: Part 2.
Just asked my mom, she said that she doesn't have any formula in place that would order the set of gymnasts and scores from highest to lowest. The problem here, is that we'd have an list of ordered pairs, (Gymnast Name/#, Score) in two columns, ordered by the Gymnast Name/#. What we need to pull from this is a list with (Gymnast Name/#, Score, Rank), ordered by rank. This can be in a separate worksheet or just moved over a few columns from the original list. Any ideas?
I've added another sheet with sample formula if you're interested.
She already has a RANK function in place that ranks scores, but the problem is that when there is a tie, it ranks the scores
1
2
2
4
and she needs it to be
1
2
2
3.
Hope that clarifies it. The way she has it set up, an IF statement like you showed won't work easily enough to be feasible. It needs to be some sort of RANK that can deal with the Total Scores as they are elsewhere in the spreadsheet.
A challenge!
Is that rank sorted as well? If it is, then what about something like:
=IF(A3=A2, B2, B2+1)
(edited in Sheet 3)
Edit: Oh heck, I just realised, that's the original formula. Still works though, and means very little modification to existing stuff (which hopefully works!) if you can get a sorted rank.
=IF(B2=C1,B2, IF(B2=C1+1, B2, C1+1))
But the list has to be sorted first or else this won't work.
EDIT: Ecco's method works fine, since two of the conditions go to the same value anyway.
Sub GenerateOrdinals() Dim cellRange As Range Set cellRange = Range(cells(1, 1), cells(Range("A65536").End(xlUp).Row, 1)) cellRange.Select 'Sort ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=cellRange, _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange cellRange .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Apply Ordinals Dim sCurrent, sLast As String Dim iOrdinal As Integer sCurrent = "" iOrdinal = 1 For Each cell In cellRange sCurrent = cell.Value If sCurrent = sLast Or sLast = "" Then cell.Offset(0, 1).Value = iOrdinal Else iOrdinal = iOrdinal + 1 cell.Offset(0, 1).Value = iOrdinal End If sLast = cell.Value Next cell End SubNOTE: For this code, the list of ratings must start in cell A1. It sorts the list and applies the numbers your mom needs.