How to calculate Z-scores in Excel

January 6, 2022
92 Views

Graphic skills

To describe a value's relationship to the mean of a group of values , sometimes we need to calculate Z-scores. The formula used to calculate a Z-score is Z=(x-µ)/σ, where

· x is the value that needs to be standardized

· μ is the mean of the given dataset

· σ is the standard deviation of the given dataset

Here are the steps.

1. Here we have a set of data that simulates the sales of coffee. Before calculating the Z-scores, we must calculate the mean and standard deviation of given data by using AVERAGE and STDEVPA formulas respectively. 2. Then we can calculate the Z-scores with the formula demonstrated above (Z=(x-µ)/σ). For example, we can calculate the Z-score of the sales of Americano, which is 244. So the Z-score in C2 will be =(B2-B9)/B10, which is -0.75733. 3. Now we can copy the formula down to calculate the Z-scores of the remaining values. Before this, remember to lock the cells containing mean and standard deviation by entering a \$ symbol before the column letter and row number. Or you can select the cells in the formula and press F4 as a shortcut. Now click and drag the formula down to complete the calculations automatically. For all, make your work easy with WPS office suite. Free download WPS Office for PC, Mobile, Windows, Mac, Android, iOS online.