# Ranking by total points and comparing rank to previous week

#### charlierb3

##### New Member
I'm creating a weekly pickem pool and want to rank players from high score to lowest and compare the current weeks ranking to the previous week

So if I enter each player's points each week in a sheet called Scores:

Book1
ABCDEFGHIJKLMNOPQRS
1PlayerWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16Week 17Total
2Player 199119
3Player 2517931
4Player 3114217
5Player 499220
6Player 59151236
7Player 6011819
8Player 71011728
9Player 81181231
10Player 980816
11Player 10221721
Scores
Cell Formulas
RangeFormula
S2:S11S2=SUM(B2:R2)

I want another sheet called Rank that looks like
Book1
ABCDE
1RankPlayerTotal PointsRank Last Week
21Player 5361
32Player 2312
42Player 8314
53Player 7283
64Player 10219
75Player 4205
86Player 1195
96Player 6197
107Player 3176
118Player 9168
Rank

I'd like each player's points to get summed each week from the scores table =Sum(!Scores(\$B\$2:\$R\$11)) and each player ranked 1-10 (ties are fine) based on the totals. But I also want to know how the player's ranking has changed compared to last week. So if it was week 3, I'd want only the scores for weeks 1 and 2 summed. A formula that says "Sum all but the last non-blank column" and rank them.

Any help is appreciated

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Ezguy4u

##### Board Regular
Just to get the ball rolling. I would go with a VBA code. Now there might be some A students that could do this using functions or formulas but not me. Let's see if this is close to what you want.

VBA Code:
``````Sub Rank_Score()
'
Sheets("Rank").Select
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D11"), Type:=xlFillDefault
Range("D2:D11").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RANK(RC[2],R2C3:R11C3,0)"
Selection.AutoFill Destination:=Range("A2:A11"), Type:=xlFillDefault
Range("A2:A11").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2:D11").Select
ActiveWorkbook.Worksheets("Rank").Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rank").Sort
.SetRange Range("A2:D11")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.CutCopyMode = False
Sheets("Rank").Select
Range("A1").Select

End Sub``````

20-09-11 rank.xlsm
ABCDEFGHIJKLM
1RankPlayerTotal PointsRank Last Week
21Player 5241Player 5245Player 118
32Player 2222Player 2222Player 222
43Player 7213Player 7217Player 315
54Player 8194Player 8195Player 418
65Player 1185Player 1181Player 524
75Player 4185Player 4188Player 611
87Player 3157Player 3153Player 721
98Player 6118Player 6114Player 819
109Player 989Player 989Player 98
1110Player 10410Player 10410Player 104
Rank

20-09-11 rank.xlsm
ABCDEFGHIJKLMNOPQRS
1PlayerWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16Week 17Total
2Player 19918
3Player 251722
4Player 311415
5Player 49918
6Player 591524
7Player 601111
8Player 7101121
9Player 811819
10Player 9808
11Player 10224
12
13
14
151
169
172
182
1912
208
217
2212
238
2417
Score

#### Peter_SSs

##### MrExcel MVP, Moderator
If your Excel 365 has the new dynamic array formulas (eg SORT(), SORTBY etc) then you could do it with worksheet formulas like this.

On the 'Scores' sheet add two columns with formulas as shown copied down then hide those two columns if you want.

charlierb3 1.xlsm
ABCDEFRSTU
1PlayerWeek 1Week 2Week 3Week 4Week 5Week 17TotalLast Week TotalLast Week Rank
2Player 199119185
3Player 2517931222
4Player 3114217157
5Player 499220185
6Player 59151236241
7Player 6011819118
8Player 71011728213
9Player 81181231194
10Player 98081689
11Player 10221721410
Scores
Cell Formulas
RangeFormula
S2:S11S2=SUM(B2:R2)
T2:T11T2=S2-LOOKUP(9^9,B2:R2)
U2:U11U2=RANK(T2,T\$2:T\$11)

On 'Rank' sheet
The formulas shown only need to be entered in row 2 of each column & the results will spill down the column (if you have those required functions)

charlierb3 1.xlsm
ABCD
1RankPlayerTotal PointsRank Last Week
21Player 5361
32Player 2312
42Player 8314
53Player 7283
64Player 102110
75Player 4205
86Player 1195
96Player 6198
107Player 3177
118Player 9169
Rank
Cell Formulas
RangeFormula
B2:B11B2=SORTBY(Scores!A2:A11,Scores!S2:S11,-1)
C2:C11C2=SORT(Scores!S2:S11,,-1)
D2:D11D2=INDEX(Scores!U2:U11,MATCH(B2#,Scores!A2:A11,0))
Dynamic array formulas.

#### Ezguy4u

##### Board Regular
I slept on my solution and clarified a few items. Just copy and paste the numbers into the weeks and run the program.

VBA Code:
``````Sub Rank_Score()
'
Sheets("Rank").Select
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D11"), Type:=xlFillDefault
Range("D2:D11").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RANK(RC[2],R2C3:R11C3,0)"
Selection.AutoFill Destination:=Range("A2:A11"), Type:=xlFillDefault
Range("A2:A11").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2:D11").Select
ActiveWorkbook.Worksheets("Rank").Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rank").Sort
.SetRange Range("A2:D11")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.CutCopyMode = False
Sheets("Rank").Select
Range("A1").Select

End Sub``````

20-09-12 rank.xlsm
ABCDEFGHIJKLMNOPQRS
1PlayerWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16Week 17Total
2Player 10
3Player 20
4Player 30
5Player 40
6Player 50
7Player 60
8Player 70
9Player 80
10Player 90
11Player 100
12
13
14
15
16991
175179
181142
19992
2091512
210118
2210117
2311812
24808
252217
Score
Cell Formulas
RangeFormula
S2:S11S2=SUM(B2:R2)

20-09-12 rank.xlsm
ABCD
1RankPlayerTotal PointsRank Last Week
2Player 10
3Player 20
4Player 30
5Player 40
6Player 50
7Player 60
8Player 70
9Player 80
10Player 90
11Player 100
Rank
Cell Formulas
RangeFormula
C2:C11C2=Score!S2

Replies
6
Views
82
Replies
18
Views
2K
Replies
1
Views
433
Replies
0
Views
362
Replies
1
Views
129

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back