This wikiHow teaches you how to calculate Z values in Microsoft Excel. In statistics, the Z value is the number of standard deviations of the data points on the standard distribution curve in the entire data set. To calculate the Z value, you need to know the mean (μ) and standard deviation (σ) of the data set. The formula for calculating the Z value is (x–μ)/σ when x data points are selected from your data set.
Step
Step 1. Open the data in Microsoft Excel
This is an app that has a green sheet icon with an "X" in front of the table. Open the Excel file with the data set for which you want to find the Z value, or enter the data into a column in a new, blank Excel document.
Step 2. Enter the average formula into a blank cell
If all the data points are recorded in an Excel spreadsheet, you can calculate the average value using the formula =AVERAGE("cell range") on the blank cells, replacing the "cell range" with the series of cells containing the points. your data.
For example, if the data points are in cells A2 through A11, and you want to calculate the average value in cell D2, you need to select cell D2 and type =AVERAGE(A2:A11)
Step 3. Enter the standard deviation formula in the blank cells
For data points recorded on an Excel spreadsheet, you can calculate their standard deviation using the formula =STDEV("cell range") on a blank cell, replacing the "cell range" with the same set of cells that contains all of your data points.
- For example, if your data points are listed in cells A2 through A11 and you want to calculate the standard deviation in cell D4, you should select cell D4 and type =STDEV(A2:A11).
- In some versions of Microsoft Excel, you need to type =STDEVA or =STDEVAP instead of =STDEV.
Step 4. Find the Z value of the data point
In the blank cell next to the cell containing the data point whose Z value you want to find, enter the formula =(datapoint - $mean)/$standard deviation, replacing "data point" with the data point cell, and replacing "average value" (mean) and "standard deviation" with absolute cell locations (a dollar sign in front of letters and numbers in a cell means the value will remain constant if you use this formula in another location).
Using our example, if you want to find the Z value of cell A2 in the column next to it, you must select cell B2 and enter the formula =(A2-$D$2)/$D$4. A dollar sign in front of letters and numbers ensures that the cell's location won't change even if you use the formula for other cells
Step 5. Apply the formula to each data point in your table
Once you've found the Z value for the first data point, you can apply the same formula to the rest of the list by dragging it across the rest of the columns. Just click the cell with the Z value you just created, then click and drag the green square in the lower-right corner of the cell to the bottom of the column. This will cause the formula to be applied to the remaining list, creating a Z value for each data point in the list.
