How to calculate Z-scores in Excel
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.