How to find z score in excel

Answer

A z-score is a standardized value that shows how many standard deviations a data point is from the mean.

Which formula defines a z-score?

A z-score is defined by the formula:

                                              $z=\frac{x-\mu }{\sigma }$

How to calculate a z-score in Excel (step-by-step)?

To calculate a z-score for a value in Excel, follow these steps:

1. Prepare your data in a single column (for example, values in cells A2:A11).

2. Compute the mean of the data in a cell, for example B1:

   1. Enter =AVERAGE(A2:A11).

3. Compute the standard deviation in a cell, for example B2.

   1. For a sample standard deviation (typical for most analyses), enter =STDEV.S(A2:A11).

   2. For a population standard deviation, enter =STDEV.P(A2:A11).

4. Compute the z-score for a single value in A2 in cell C2:

   1. Manual formula: =(A2 - $B$1) / $B$2.

   2. Built-in function: =STANDARDIZE(A2, $B$1, $B$2).

5. Copy the formula in C2 down the column to get z-scores for all rows.

Which Excel functions are appropriate and when?

1. AVERAGE(range) — to get the mean \mu.

2. STDEV.S(range) — to get the sample standard deviation \sigma when your data is a sample.

3. STDEV.P(range) — to get the population standard deviation when your data is the entire population.

4. STANDARDIZE(x, mean, standard_dev) — returns the z-score directly.

5. NORM.S.DIST(z, TRUE) — returns the cumulative probability (the area to the left of the z-score).

6. NORM.S.INV(probability) — returns the z-score for a given cumulative probability.

Are there differences between Windows and Mac versions of Excel?

Yes.

1. Function names are the same in modern Excel on Windows and Mac.

2. Older Mac Excel versions might not have STDEV.S and STDEV.P; they used STDEV for sample standard deviation.

3. STANDARDIZE, NORM.S.DIST, and NORM.S.INV require Excel 2010 or later on Windows and Excel 2011 or later on Mac for full parity.

Example (numeric) — how to compute a z-score manually and with Excel

A value is x = 85, sample mean is \mu = 75, sample standard deviation is \sigma = 10.

1. Manual calculation:

       $z=\frac{85-75}{10}=1$                     

2. Excel:

   1. Put 85 in A2, put the data range in A2:A11 (or compute mean 75 in B1 and 10 in B2).

   2. Manual cell formula: =(A2 - $B$1) / $B$2.

   3. Built-in formula: =STANDARDIZE(A2, $B$1, $B$2).

3. To get the cumulative probability for z = 1 in Excel: =NORM.S.DIST(1, TRUE).

How to interpret the z-score?

A z-score greater than 0 means the value is above the mean.
A z-score less than 0 means the value is below the mean.
A z-score of 1 means the value is 1 standard deviation above the mean.

Common pitfalls and best practices

1. Use STDEV.S for sample data and STDEV.P for population data.

2. Lock mean and standard deviation cells with absolute references ($B$1, $B$2) when copying formulas.

3. Use STANDARDIZE for clarity and fewer mistakes when available.

4. Check data for outliers before interpreting z-scores, because outliers inflate standard deviation.

How to get a p-value from a z-score in Excel?

To get the one-tailed p-value (area to the right of z):

1. Compute cumulative left-tail probability: =NORM.S.DIST(z, TRUE).

2. One-tailed p-value (right tail) = =1 - NORM.S.DIST(z, TRUE).
   To get a two-tailed p-value = =2 * (1 - NORM.S.DIST(ABS(z), TRUE)).

Relevant formulas (LaTeX) for copy-paste

Mean (sample mean):

                      $\overline{x}=\frac{1}{n}\sum _{i=1}^n{x}_i$

Sample standard deviation:

                      $s=\sqrt{\frac{1}{n-1}\sum _{i=1}^n(x_i-\overline{x}{)}^2}$

Z-score:

                    \(z = \frac{x - \mu}{\sigma}\)

Quick cheat-sheet (1-line formulas for Excel)

1. Mean: =AVERAGE(A2:A11)

2. Sample SD: =STDEV.S(A2:A11)

3. Z-score (manual): =(A2 - AVERAGE($A$2:$A$11)) / STDEV.S($A$2:$A$11)

4. Z-score (function): =STANDARDIZE(A2, AVERAGE($A$2:$A$11), STDEV.S($A$2:$A$11))

5. Left-tail probability: =NORM.S.DIST(z, TRUE)

6. Two-tailed p-value: =2 * (1 - NORM.S.DIST(ABS(z), TRUE))

Yes · 0 No · 0 Add Comment · 0

Add ·

Report