Results 1 to 11 of 11

20131213, 23:54 #1
 Join Date
 Dec 2013
 Posts
 2
 Thanks
 3
 Thanked 0 Times in 0 Posts
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 B1J1. 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 reenter all of the data.
Thanks in advance for any assistance.

20131214, 01:58 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,570
 Thanks
 44
 Thanked 73 Times in 69 Posts
Welcome to the lounge.
If the point values are steadily decreasing, try the array formula: =SUM(26B1:J1) in K (and fill down).
The array formula must be entered using: CTRL+Shift+Enter

The Following User Says Thank You to kweaver For This Useful Post:
superman7515 (20131214)

20131214, 02:00 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,922
 Thanks
 152
 Thanked 747 Times in 679 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

The Following User Says Thank You to Maudibe For This Useful Post:
superman7515 (20131214)

20131214, 02:06 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,922
 Thanks
 152
 Thanked 747 Times in 679 Posts
Nicely done KW!

20131214, 02:15 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,570
 Thanks
 44
 Thanked 73 Times in 69 Posts
What are you doing awake in PA at this hour Eastern? GRIN

20131214, 02:27 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,922
 Thanks
 152
 Thanked 747 Times in 679 Posts
I'm in the doghouse so nothing better to do on a Friday night!

20131214, 05:38 #7
 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)*26SUM(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

The Following User Says Thank You to sdckapr For This Useful Post:
superman7515 (20131214)

20131214, 06:46 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,900
 Thanks
 420
 Thanked 1,585 Times in 1,434 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

20131214, 08:58 #9
 Join Date
 Dec 2013
 Posts
 2
 Thanks
 3
 Thanked 0 Times in 0 Posts
Thank you all for your assistance, I truly appreciate it.

20131214, 09:01 #10
 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!
Steve

20131214, 11:53 #11
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,922
 Thanks
 152
 Thanked 747 Times in 679 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; 20131214 at 13:30. Reason: Provide error checking