Z-Score in Excel: Overview
A z-score tells you how many standard deviations a certain piece of data (“X”) is above or below the mean. A z-score in Excel can quickly be calculated using a basic formula. The formula for calculating a z-score is z=(x-μ)/σ, where μ is the population mean and σ is the population standard deviation (note: if you don’t know the population standard deviation or the sample size is below 6, you should use a t-score instead of a z-score). In other words, the formula is z = (data point – mean) / standard deviation.
Z-Score in Excel: Steps
Watch the video or read the steps below:
Sample question: You take the GRE and scored 650 in the verbal section of the test. Calculate a z-score in Excel to determine how good your score is compared to the general population of test takers. The population mean (μ) is 469 and the population standard deviation (σ) is 119.
Step 1: Type the population mean into a blank cell in Excel. For this example, type “469″ into cell A2. Optional: type the word “mean” as a column header in cell A1 so you remember what the value in cell A2 stands for.
Step 2: Type the population standard deviation into a blank cell. For this example, type “119″ into cell B2. Optional: type the words “standard deviation” as a column header in cell B1 so you remember what the value in cell B2 stands for.
Step 3: Type the X-value (in this sample problem, X is your GRE score) into a blank cell. For this example, type “650″ into cell B2. Optional: type the words “X” as a column header in cell B1 so you remember what the value in cell B2 stands for.
Step 4: Type the following formula into an empty cell:
Step 5: Press “Enter.” The z-score will appear in cell D2: The z-score of 1.521008 in this sample problem indicates your GRE score was 1.521008.
That’s it! You’ve found a z-score in Excel.
Tip: Once you’ve entered the formula once, you can use it over and over again. Just type a new mean, standard deviation and X value into the relevant boxes.