1. ## 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. 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. 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. Nicely done KW!

7. What are you doing awake in PA at this hour Eastern? GRIN

8. I'm in the doghouse so nothing better to do on a Friday night!

9. 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. 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!

12. Thank you all for your assistance, I truly appreciate it.

13. 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. 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```

#### Posting Permissions

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