Don't like the snow? You can make a bookmark with the following text instead of a url: javascript:snowStorm.toggleSnow(). Clicking it will toggle the snow on and off.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Excel Formula Help - RANK formula

AlpineAlpine Registered User regular
edited May 2009 in Help / Advice Forum
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.

Alpine on

Posts

  • ecco the dolphinecco the dolphin Registered User regular
    edited May 2009
    Alpine wrote: »
    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

    It will rank them as 1 2 2 3, rather than 1 2 3 4.

    Any ideas?

    (I know that there really isn't any need for this, but it's an important meet and if the announcer reads the printout of the scores wrong, it could change whether or not a gymnast gets the medal they deserve.)

    Thanks guys.

    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.

    Penny Arcade Developers at PADev.net.
  • AlpineAlpine Registered User regular
    edited May 2009
    That looks like it would likely work. I was thinking that I had to use some sort of ranking formula, never thought of doing it that way. Thanks a lot.

    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?

  • ecco the dolphinecco the dolphin Registered User regular
    edited May 2009
    Use the sort function?

    I've added another sheet with sample formula if you're interested.

    Penny Arcade Developers at PADev.net.
  • AlpineAlpine Registered User regular
    edited May 2009
    Okay, forget that part. I just saw the spreadsheet she's working with, and she explained what she wants it to do more clearly.

    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.

  • ecco the dolphinecco the dolphin Registered User regular
    edited May 2009
    Alpine wrote: »
    The way she has it set up, an IF statement like you showed won't work easily enough to be feasible.

    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.

    Penny Arcade Developers at PADev.net.
  • OrogogusOrogogus San DiegoRegistered User regular
    edited May 2009
    This is what I came up with, based on values in column A, ranks in column B and rank 1 automatically placed in cell C1. The following goes in cell C2 and is copied downwards:

    =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.

  • DrezDrez Registered User regular
    edited May 2009
    Quick and dirty macro I just wrote to do this:
    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 Sub
    

    NOTE: For this code, the list of ratings must start in cell A1. It sorts the list and applies the numbers your mom needs.

    steam_sig.png
Sign In or Register to comment.