askvity

How to Find Z-Score in Google Sheets?

Published in Google Sheets Functions 3 mins read

You can find the Z-score in Google Sheets by calculating the standard deviation and mean of your data and then applying the Z-score formula to each data point. Here's a breakdown of the process:

Steps to Calculate Z-Score in Google Sheets

  1. Calculate the Mean (Average): Use the AVERAGE() function. For example, if your data is in cells A1 to A10, the formula would be:

    =AVERAGE(A1:A10)

  2. Calculate the Standard Deviation: Use the STDEV() function (for sample standard deviation) or STDEV.P() (for population standard deviation). Using the same data range as above:

    =STDEV(A1:A10) or =STDEV.P(A1:A10)

  3. Calculate the Z-Score for Each Data Point: For each data point, subtract the mean from the data point and divide the result by the standard deviation. Assuming the first data point is in cell A1, the mean is in cell B1, and the standard deviation is in cell B2, the formula would be:

    =(A1-B1)/B2

    You can then drag this formula down to apply it to all data points.

Example

Let's say your data is as follows:

Data Point
70
85
90
65
75

  1. Enter the data: Enter the data in cells A1 to A5.

  2. Calculate the Mean: In cell B1, enter the formula =AVERAGE(A1:A5). The result will be 77.

  3. Calculate the Standard Deviation: In cell B2, enter the formula =STDEV(A1:A5). The result will be approximately 10.77.

  4. Calculate the Z-Scores:

    • In cell C1, enter the formula =(A1-B1)/B2. This calculates the Z-score for the first data point (70). The result will be approximately -0.65.
    • Drag the fill handle (the small square at the bottom right of cell C1) down to cell C5. This will automatically apply the formula to the remaining data points, calculating their respective Z-scores.

Your spreadsheet should now look something like this:

Data Point (A) Mean (B) Standard Deviation (C) Z-Score (D)
70 77 10.77 -0.65
85 0.74
90 1.21
65 -1.11
75 -0.19

In summary, use the AVERAGE() and STDEV() functions in Google Sheets to calculate the mean and standard deviation of your dataset, then apply the Z-score formula (data point - mean) / standard deviation to determine the Z-score for each value.

Related Articles