ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Intro to Sports Analytics
2
3
4
Kyle EvansTrinity College
5
Basketball (Lineup Analysis)
6
7
8
9
Notes/Steps
10
11
To analyze how players and lineups perform, we have all lineup combinations that have played at least 25 minutes together (so far) for the 2022-23 Boston Celtics. The data includes how many minutes they played together and their +/- (plus/minus) which is the point differential vs. opponents.
12
13
14
15
Goal #1: Analyze impact of a single player when they are on vs. off the court. (Celtics22_23 sheet)
16
1)
For a specific player, we want to only consider lineups in which they appear.
17
We will use a logical function to help with this.
18
In cell H1, type the name of any player as it appears in the data.
19
You can also set up Data Validation to make a drop-down menu of player names.
20
Now we want a function that checks if the player is in the lineup (any of the 5 spots) or not.
21
In cell H2, type the function: =if(or(A2=$H$1,B2=$H$1,C2=$H$1,D2=$H$1,E2=$H$1),1,0)
22
Then copy the formula down the rest of the column (drag or double-click black cross).
23
This should place a 1 next to any lineup with that player and a 0 when the player doesn't appear.
24
2)
Now we want to add the minutes and +/- for when a player was in vs. out of the game.
25
In cell L3, type the function: =sumif(H2:H26,1,F2:F26) to add minutes when the player is in the game.
26
Repeat for cell M3 with G2:G26 (instead of F2:F26) as the summed cells.
27
Then repeat for cells L4 and M4 but with 0 instead of 1 for when the player is out of the game.
28
In cell N3, enter the formula =48*M3/L3 to get a +/- per 48 minutes when on the court.
29
Drag down to N4 to get a +/- per 48 minutes when off the court.
30
In cell N5, subtract the two (N3-N4) to get a +/- per 48 minutes differential.
31
3)
Now we can use a one-way data table to complete the formulas for all players at once.
32
In cell L9 ("In"), type =N3 (copies formula).
33
In cell L10 ("Out"), type =N4.
34
In cell L11 ("Diff"), type =N5.
35
Then highlight the range K9:N21, go to Data ® What-If Analysis ® Data Table.
36
Leave the row input cell empty and click H1 (player name) for the column input cell.
37
4)
Enter last name only for players.
38
The Celtics outscore their opponents best when which player is on the court?
0
39
By how many points per 48 min? Round to nearest whole number.
0
40
The Celtics outscore their opponents worst when which player is on the court?
0
41
By how many points per 48 min? Round to nearest whole number.
0
42
The Celtics outscore their opponents best when which player is off the court?
0
43
By how many points per 48 min? Round to nearest whole number.
0
44
The Celtics outscore their opponents worst when which player is off the court?
0
45
By how many points per 48 min? Round to nearest whole number.
0
46
Which two players have the largest differential in impact on vs. off the court?
0
47
(#1 differential in top box, #2 in bottom box)
0
48
By how many points per 48 min? Round to nearest whole number.
0
49
50
51
Goal #2: Analyze impact of pairs of players when they are on or off the court together. (Pairs sheet)
52
5)
In cells H1 and I1, type in 2 different player names as they appear in the data.
53
Then use logical functions to have a 1 appear for each player appearing in a lineup and 0 if not.
54
6)
In cell L2, type the function: =sumifs(F2:F26,H2:H26,1,I2:I26,1) to add up minutes when both appear.
55
Repeat for cell M2 with G2:G26 (instead of F2:F26) as the summed cells.
56
Then repeat for cells L3 and M3 but with 0 instead of 1 for when both players are out of the game.
57
In cell N2, enter the formula =48*M2/L2 to get a +/- per 48 minutes when both on the court.
58
Drag down to N3 to get a +/- per 48 minutes when both off the court.
59
7)
Now we can use a two-way data table to complete the formulas for all pairs at once.
60
In cell P2, type =N2.
61
Then highlight the range P2:AC15, go to Data ® What-If Analysis ® Data Table.
62
For row input cell click H1 and column input cell click I1.
63
Repeat for both out, starting with =N3 in cell P18.
64
A gradient conditional formatting will help see which combinations are most/least effective together.
65
8)
Which two players give the Celtics the best per 48 min differential when on the court together?
66
Enter last name only and in alphabetical order.
0
67
0
68
By how many points per 48 min? Round to nearest whole number.
0
69
Which two players give the Celtics the worst per 48 min differential when on the court together?
70
Enter last name only and in alphabetical order.
0
71
0
72
By how many points per 48 min? Round to nearest whole number.
0
73
Which two players give the Celtics the best per 48 min differential when off the court together?
74
Enter last name only and in alphabetical order.
0
75
0
76
By how many points per 48 min? Round to nearest whole number.
0
77
Which two players give the Celtics the worst per 48 min differential when off the court together?
78
Enter last name only and in alphabetical order.
0
79
0
80
By how many points per 48 min? Round to nearest whole number.
0
81
82
83
Goal #3: Calculate adjusted +/- player ratings. (Adjusted +- sheet)
84
9)
I calculated the actual point differential per minute in column H.
85
We want to solve for the best set of player ratings to predict the lineup point differentials.
86
Start by placing educated guesses in the player ratings.
87
We use a v-lookup function to match players to their ratings in each lineup.
88
In cell I2, type the formula: =vlookup(A2,$Q$2:$R$15,2,false) to lookup the exact player name in the table and get their rating (rating comes from the 2nd column in the table).
89
90
Drag the formula down and across to get the ratings for all 5 players in all lineups.
91
10)
In cell N2 (PPM Pred.) enter the formula: =sum(I2:N2)/48 to get the predicted point differential per minute.
92
Column O calculates the weighted squared error which we want to be as small as possible for more accurate predictions.
93
94
In cell O2, type the formula: =F2*(H2-N2)^2
95
Then in cell O27, add up the values in that column to get a total error.
96
11)
Make sure the Excel Solver Add-in is activated (PC: File ® Options ® Add-ins).
97
In the Excel Solver dialog box, set objective cell to O27 (total error), click min (to minimize its value), by changing variable cells - highlight the ratings in R3:R14, and we want to allow negative values so uncheck the "make values non-negative" option, and click solve.
98
99
100
These values represent the projected point differential for that player playing an entire game against an average opponent. This includes both offense and defense differences.