# Help finding formula for zscore spreadsheet

#### johnlocke3

##### New member
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

##### Elite Member
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 Z[SUB]r[/SUB] (raw z-FG%) is what is measured. This is turned into a (standard) score Z (z-FG%) by
Z = $$\displaystyle \frac{Z_r\, -\, \mu}{\sigma}$$

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

EDIT:As a pragmatic view of the differences, there may be some round off errors. Suppose that first pair of Z[SUB]r[/SUB] 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.

Last edited: