Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Excel Spreadsheet for Top 25 Rankings

    So I have a historical list of Top 25 rankings for some local sports teams. It's a weekly poll that has been run for many years and I want to come up with an aggregate ranking. For example, for 2000, Team A was ranked 1, 1, 9, 13, 8, 3, 3, 10, 8. So I have the team name in A1 with the rankings in B1-J1. In cell K1, I want to have their total points from all weeks where 1 = 25 points, 2 = 24 points, 3 = 23 points, etc etc.

    I'm having no luck in finding anything online to show me how to do this. Is there an easier way other than just going through and entering 25 instead of 1, 24 instead of 2, etc? I have several decades to do, so I would prefer something I can just copy and paste from sheet to sheet instead of having to re-enter all of the data.

    Thanks in advance for any assistance.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Welcome to the lounge.

    If the point values are steadily decreasing, try the array formula: =SUM(26-B1:J1) in K (and fill down).
    The array formula must be entered using: CTRL+Shift+Enter

  3. The Following User Says Thank You to kweaver For This Useful Post:

    superman7515 (2013-12-14)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Superman,

    Not sure if this is what you are looking for but here is a user defined function that will convert the rank to points. Enter the formula into the cell then copy down:

    Cell K2 has the formula =Ranking(B2,C2,D2,E2,F2,G2,H2,I2,J2) then copy down

    It will convert each of the rankings to points then total them across in column K. Use the UDF to set up your spreadsheet as you see fit

    HTH,
    Maud

    Scoring1.png

    Code:
    Public Function Ranking(ParamArray Nums() As Variant) As Variant
    Dim points As Integer
    Dim I As Integer
    points = 0
    For I = LBound(Nums) To UBound(Nums)
        If IsNumeric(Nums(I)) = True Then
            points = points + 26 - Nums(I)
        End If
    Next I
    Ranking = points
    End Function

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    superman7515 (2013-12-14)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Nicely done KW!

  7. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    What are you doing awake in PA at this hour Eastern? GRIN

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    I'm in the doghouse so nothing better to do on a Friday night!

  9. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It can also be done with the regular formula:
    =count(B1:J1)*26-SUM(B1:J1)

    This formula has the advantage of also ignoring any blanks in the range. The array formula will calculate a blank as if it were a zero and give the team 26 points...

    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    superman7515 (2013-12-14)

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Brilliant! I just have to ask, what is your educational background or you just a natural math wizard?

    BTW: The avatar is most appropriate!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #9
    New Lounger
    Join Date
    Dec 2013
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you all for your assistance, I truly appreciate it.

  13. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    what is your educational background or you just a natural math wizard?
    BTW: The avatar is most appropriate!
    The avarar is very appropriate, it looks a lot like me and I am Chemist. My educational background and being a natural math wizard are not mutually exclusive: I have a PhD in Organic Chemistry and I have always had a natuarl affinity to math (and other science) [as well as Science Fiction as you may have guessed from earlier conversations]. My affinity (or wizardry) in math is one of the reasons I became scientist.

    Steve

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    SM,

    The amended UDF will ignore text, blanks, and values greater than 25 or less than 1, and non integers. It will also allow the cells to be entered as a range:

    Formula in K1 = Ranking(B1:J1) then copy down

    Scoring3.png

    Code:
    Public Function Ranking(rng As Range) As Variant
    Dim points As Integer
    Dim I As Integer
    points = 0
    For Each Cell In rng
        If IsNumeric(Cell.Value) And Cell.Value <> "" And _
        Cell.Value > 0 And Cell.Value < 26 Then
            If Cell.Value = Int(Cell.Value) Then
                points = points + 26 - Cell.Value
            End If
        End If
    Next Cell
    Ranking = points
    End Function
    Last edited by Maudibe; 2013-12-14 at 13:30. Reason: Provide error checking

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •