How to Calculate Z-Score in Excel:
As you know, there are many mathematical calculations that you can perform with Microsoft Excel. In this tutorial, we will walk you through the steps involved in calculating Z-score in Excel.
If you are using Excel for the first time, we recommend that you take five minutes to read this Microsoft Excel Beginner Tutorial. You’ll learn how essential functions work, navigation shortcuts, creating workbooks, formatting data, and everything there is to know about using a spreadsheet program as a beginner.
When you’re done with that, skip to the next section to learn how to calculate the Z score in Excel. But first, let’s take a brief look at the Z-score, its uses, why you might need to calculate one, and how to do it.
What is a Z-Score?
The Z-score (also known as the “standard score”) is a metric that highlights the relationship between values in a distribution. More precisely, it describes the position of values in a dataset with respect to their mean and standard deviation. Z-Score allows precise measurement and comparison of values across a dataset.
The mathematical formula for calculating the Z-score is (x-μ) /; where x = cell value, µ = mean, and = standard deviation.
Companies sometimes use Z-scores to anticipate and anticipate impending bankruptcy. Additionally, it is a great metric to gauge the financial position of an institution. Researchers also use Z-scores to compare observations from different samples or populations.
How to Calculate Z-Score in Excel
Since the Z-score is a function of the mean and standard deviation, you must first calculate the mean and standard deviation of your dataset. Although you can work out the mean and standard deviation in any cell, we have created dedicated columns for “Mean” and “Standard Deviation” in our worksheet. We have also created a column for “Z-Score”.
Our sample document contains the performance rating of 10 employees in a paper company. Now let’s calculate the Z-Score of the rating of the employees.
calculate mean average
To calculate the mean average for your dataset, type =average(select , first value In the dataset, press column keyselect , last price Within the dataset range, press closing bracket key, and press To enter. The formula should look like below:
You should see the dataset’s mean or median value in the cell that you entered in the formula.
Calculate Standard Deviation
Excel also makes it very easy Calculate the standard deviation of your dataset In just a few clicks.
Select a cell in the “Standard Deviation” column, type =STDEVP(, then select first value In range, press column key, select the last value, enter closing bracket, and press To enter. In case you are in doubt, the resulting formula should be similar to the formula below:
Calculate Z-Score in Excel: Method 1
Excel has a standard function that provides the z-score of a dataset in a distribution. Select the first cell in the Z-score column and follow the steps below.
- go to sources tab and select more threads.
- hover your mouse over statistical option and choose testify.
This will launch a new Function Arguments window where you will be able to calculate the Z-score of the distribution.
- Enter the cell reference of the first value in the “X” field.
- Enter the cell reference of the arithmetic average in the “Mean” field and press F4 on your keyboard to lock the cell reference.
- Finally, enter the cell reference of the standard deviation in the “Standard_dev” field and press F4 To lock the cell reference. The tool will display a preview of the Z-value. press Ok To move forward.
To get the Z-score for other values, move the cursor to the lower-right corner of the cell and drag plus (+) icon below the column.
Excel will copy the formula down the column and automatically generate z-scores for the other values in the respective rows.
Calculate Z-Score in Excel: Method 2
As mentioned earlier, you can get the z-score of a datapoint by subtracting the dataset’s mean from the datapoint and dividing the result by the standard deviation. Using (x-µ) / , you can calculate the z-score in Excel by inputting these values manually.
- Select the first cell in the Z-score column, type the equal sign (=) after that open bracket, and select the first value in the column containing the datasets for which you want to calculate the Z-Score. Afterwards, type a hyphen, select the arithmetic mean, press F4 To make the mean absolute/constant, press More closing bracket Mark. Finally, press forward slash (/) key, select the standard deviation, and press F4 To lock the cell reference.
The final formula should look like this: =(B2-$C$2)/$D$2. press To enter to execute the formula.
Note that the formula will only calculate the Z-Score for the first value in the selected cell.
- Move and drag your mouse to the lower-right corner of the first Z-Score cell plus (+) icon below the column.
Interpreting the Z-score
Your dataset will most likely have a mix of negative and positive Z-Scores. A positive Z-score indicates that the value/score is higher than the mean of the dataset. A negative Z-score, of course, suggests the opposite: the value is below average. If a datapoint has a Z-score of zero (0), it is because its value is equal to the arithmetic mean.
The larger a data point, the higher its Z-Score. Look at your worksheet and you’ll find that smaller values tend to have lower Z-scores. Similarly, values smaller than the arithmetic mean will have negative Z-scores.
For example, in our sample worksheet, you’ll find that “Michael” had the highest rating (78) and the highest Z-score (1.679659). On the other hand “Dwight” and “Kevin” both had the lowest ratings (34) and lowest Z-scores (-1.59047).
Become an Excel Expert: Tutorial Repository
You now know how to calculate the Z-score of a data set. Leave a comment below if you have questions or other useful tips regarding calculating Z-score in Excel.