PDA

View Full Version : Help finding formula for zscore spreadsheet

johnlocke3
10-30-2015, 01:21 PM
Hello, I was looking for a little help with a spreadsheet I was trying to recreate using zscores. This sheet was used to judge defenses in basketball, but I am having a tough time figuring out what the original creator used to make the calculations. I understand how to get the means and S.D. for the columns listed, and the zscore for (raw z-FG%). But under z-FG% (last column) it looks like he may have used several sources of data to come up with the answers. So, I'm looking for the formula used to calculate z-FG% of 0.4 for row team A. I hope I am posting this in the right spot! Thanks

team
Opp FG%
raw z-FG%
Opp FGA
FGA as % of mean
z-FG%

A
45.2
0.5
82.6
98.10%
0.4

B
44.5
0
84.1
99.90%
0

C
45.5
0.6
88.5
105.10%
0.7

D
44.2
-0.2
83.9
99.60%
-0.2

E
42.9
-1.1
85.9
102.00%
-1.1

F
45.1
0.4
83.2
98.80%
0.4

G
45.9
0.9
83.4
99.00%
0.9

H
45.9
0.9
83.6
99.30%
0.9

I
44.8
0.2
83.3
98.90%
0.2

J
43.1
-0.9
84.6
100.50%
-0.9

K
43.3
-0.8
90
106.90%
-0.9

L
41.1
-2.2
83.6
99.30%
-2.2

M
42.8
-1.1
83.1
98.70%
-1.1

44.3
-0.1
82.1
97.50%
-0.1

44.4
-0.1
80.3
95.40%
-0.1

44.4
-0.1
81.8
97.10%
-0.1

43.8
-0.5
81.4
96.70%
-0.5

46.7
1.4
88.1
104.60%
1.5

44
-0.3
85.4
101.40%
-0.3

45.4
0.6
83.3
98.90%
0.6

44.1
-0.3
88
104.50%
-0.3

46.7
1.4
81.2
96.40%
1.4

43.6
-0.6
84.2
100.00%
-0.6

43.2
-0.9
86.2
102.40%
-0.9

46.3
1.2
87.6
104.00%
1.2

46.9
1.6
87.2
103.60%
1.6

45.8
0.8
84.1
99.90%
0.8

46.7
1.4
83.1
98.70%
1.4

40.8
-2.4
79.3
94.20%
-2.3

44
-0.3
82.9
98.50%
-0.3

Means:

44.5

84.2

Standard Deviations:

1.5

2.5

Ishuda
10-30-2015, 06:17 PM
Hello, I was looking for a little help with a spreadsheet I was trying to recreate using zscores. This sheet was used to judge defenses in basketball, but I am having a tough time figuring out what the original creator used to make the calculations. I understand how to get the means and S.D. for the columns listed, and the zscore for (raw z-FG%). But under z-FG% (last column) it looks like he may have used several sources of data to come up with the answers. So, I'm looking for the formula used to calculate z-FG% of 0.4 for row team A. I hope I am posting this in the right spot! Thanks

team
Opp FG%
raw z-FG%
Opp FGA
FGA as % of mean
z-FG%

A
45.2
0.5
82.6
98.10%
0.4

B
44.5
0
84.1
99.90%
0

C
45.5
0.6
88.5
105.10%
0.7

D
44.2
-0.2
83.9
99.60%
-0.2

E
42.9
-1.1
85.9
102.00%
-1.1

F
45.1
0.4
83.2
98.80%
0.4

G
45.9
0.9
83.4
99.00%
0.9

H
45.9
0.9
83.6
99.30%
0.9

I
44.8
0.2
83.3
98.90%
0.2

J
43.1
-0.9
84.6
100.50%
-0.9

K
43.3
-0.8
90
106.90%
-0.9

L
41.1
-2.2
83.6
99.30%
-2.2

M
42.8
-1.1
83.1
98.70%
-1.1

44.3
-0.1
82.1
97.50%
-0.1

44.4
-0.1
80.3
95.40%
-0.1

44.4
-0.1
81.8
97.10%
-0.1

43.8
-0.5
81.4
96.70%
-0.5

46.7
1.4
88.1
104.60%
1.5

44
-0.3
85.4
101.40%
-0.3

45.4
0.6
83.3
98.90%
0.6

44.1
-0.3
88
104.50%
-0.3

46.7
1.4
81.2
96.40%
1.4

43.6
-0.6
84.2
100.00%
-0.6

43.2
-0.9
86.2
102.40%
-0.9

46.3
1.2
87.6
104.00%
1.2

46.9
1.6
87.2
103.60%
1.6

45.8
0.8
84.1
99.90%
0.8

46.7
1.4
83.1
98.70%
1.4

40.8
-2.4
79.3
94.20%
-2.3

44
-0.3
82.9
98.50%
-0.3

Means:

44.5

84.2

Standard Deviations:

1.5

2.5

If I'm understanding you correctly, the raw score Zr (raw z-FG%) is what is measured. This is turned into a (standard) score Z (z-FG%) by
Z = \frac{Z_r\, -\, \mu}{\sigma}

Just based on the two scores, a (close to) best fit for \mu and \sigma appears to be \mu = 0, \sigma = 1.

EDIT:As a pragmatic view of the differences, there may be some round off errors. Suppose that first pair of Zr and Z was supposed to be 0.45 and the person filling out the 'raw' colume rounded up to 0.5 and the person filling out the 'standard' column rounded down to 0.4.