In this tutorial you learn How to Calculate Z scores in Google Sheets

## Calculate Z scores in Google Sheets

To calculate the Z scores in Google Sheets you can use the formula which will compound a Z score “**=(DataValue-Mean)/StDev**“.

**How to Calculate Z scores in Google Sheets : Step by Step Guide**

Here are the steps to **Calculate Z scores in Google Sheets**

**Step 1 : Open Google Sheets and prepare your dataset**

In this example we will use the below dataset with marks scored out of 10

**Step 2 : Calculate the Mean and Standard deviation**

First we will calcuate the **Standard deviation and Mean** using the **STDEVP** and the **AVERAGE** formula

To calculate the standard deviation ,Click on cell D2 under Heading **STDDEV **and type the below formula in fx function area (adjust the formula as per your requirement.)

**=STDEVP(A2:A14)**

To calculate the mean Click on the Cell C2 under Heading Mean and type the below formula (adjust the formula as per your dataset ) and press enter

**=AVERAGE(A2:A14)**

Now we have calculated both the standard deviation and Mean .Value for Standard deviation is 2.058321255 and Mean is 5.384615385

**Step 3 : Calculate the Z-Score**

To calculate the Z-Score for the first value in our data set we will use the below formula

Z-score = (Data Value - Mean) / Standard Deviation

Based on above formula we will type the following syntax in cell B2 and press enter

**=(A2–$C$2)/$D$2**

Confirm the suggested auto-fill to calculate the Z-score for remaining values in your dataset.

Z-score has been calculated for all the values in your dataset.

- You will notice a dollar Sign $ being used in the formula which are used as absolute marked in the formula .

- They direct the function to always check in a particular cell for value even if somone copies the formula to other cell.

In our formula **=(A2–$C$2)/$D$2** it will always look for the mean value in Cell C2 and Standard deviation on cell D2.

** How to interpret the Calculated Z-Score result **

The Z score calculation is used to determine how many standard deviations there are between a value and the dataset’s mean value.

In our example, the Calculated values for standard deviation and mean were :

**Standard deviation**: 2.058321255**Mean**: 5.384615385

The calculated Z score for the first value in our data set was

(3-5.384615385) / 2.058321255 = **-1.158524394**

This indicates that the data value **“3”** is **-1.158524394** standard deviations below the mean.

While the value indicates how many standard deviations the value is from the mean, the negative symbol indicates that the value is below the mean.

The calculated Z score for the second value in our data set was

(6-5.384615385) / 2.058321255 = 0.2989740371

This indicates that the data value **“6”** is 0.2989740371 standard deviations below the mean.

**How to Calculate Z scores in Google Sheets (Conclusion)**

In the above tutorial we have shown you a step by step guide to calculate Z score in Google Sheets.

We hops this tutorial on **How to Calculate Z scores in Google Sheets** was useful.

Related articles :

How to Insert Spin Button in Google Sheets ( Easy Guide )

How to Freeze Rows in Google Sheets

How to Calculate Square Root and Cube Root in Google Sheets(Quick & Easy Guide)

How to use SUMSQ Function in Google Sheets(Quick & Easy Guide )