How to Calculate Z-Score in Excel?

The z score in Excel is calculated using the data point minus the mean divided by the standard deviation. To calculate z-score in Excel, we first need to write our data in columns one below the other. We can calculate the z value in Excel using specific formulas. The advantage of using Excel formulas is that we can customize the formulas for large amounts of data. It allows us to calculate faster and easier. Let’s learn how to make this step by step.

Step 1: determine your dataset

Suppose we calculate the final exams for English language and literature students and say their scores are: 72, 90, 87, 65, 50, 78, 82, 63, 55, and 80. These ten data points are from column A. and from row 2 to column A, row 11. I chose to write “Test results” in A1, “Z-Score” in B1, and “Z-Score” in C1 in our sheet to keep it simple. I have added two columns with the Z score, as I will show you how to calculate it both manually and automatically.

Tip: When you write formulas, the cell you refer to is highlighted, not double-checking everything you write.

ALSO READ: How to wave on Facebook Messenger?

Step 2: calculate the mean

Since we are using the mean to get the z-score of our data set, we need to calculate the mean. Average means the mathematical average in a group of data. Mathematically, this means adding up all the numbers and dividing by 10. However, in Excel, we use a simple formula to calculate quickly. Let’s write “mean” in a cell other than columns A, B, or C. It can be any other row and column. I’ll choose F2 for this example. Click the blank cell next to it, which for us is G2, and enter the formula in the formula area above our sheet to calculate the average:

= AVERAGE (A2: A11)

Hit enter, and we have the average. It’s 72.2

ALSO READ: EasyAntiCheat.exe on your computer?

Step 3: calculate the standard deviation

Then we calculated the standard deviation. The standard deviation is the measure that shows us how far each data is from the mean. Statistically, we calculate it by subtracting all the numbers from the mean and squaring them so that all results are positive instead of negative. These numbers are added up and divided by the number of people in our data set (10 in this example). In fact, Excel has a formula for that too. Let’s write “standard deviation” in one cell. I chose F3 for our practice. We will empty the cell next to it, G3, and enter our formula in the formula space above our sheet.

For those using the latest versions of Excel, the formula should be:

= STDEV.S (A2: A11)

However, in previous versions, we used the STDEV formula instead of the STDEV.S formula. Which is:

= STDEV (A2: 11)

Click and enter, and the standard deviation is approximately 13.55

Tip: If you are not sure if you’re using the latest versions or you’re not worried, Excel will ask you to switch to the formula you are using for an up-to-date formula.

ALSO READ: How do I Enable Reddit Dark Mode

STEP 4: Manually calculate the Z-Score

Not that we have computed the mean and formula, we can start calculating the Z-score. We can start getting our z-score from the first person, 72, to ourselves. We’re going to subtract the mean of the individual and divide it into the standard deviation. A quick reminder that our average was in cell G2, and the standard deviation value was in cell G3.

So we click on the empty B2 cell and enter it

= (A2-G2) / G3

And that gives us the result -0.01476. The z-score for our first person is -0.01476. Now we can calculate other people’s Z values ​​by entering the same formula, except we enter the cell we are calculating for instead of A2. Although, there is an easier way to calculate them all.

We click on the cell with the formula, and we lock the standard deviation value cell and the mean value cell. To do this, we use the dollar symbol ($). We put a $ sign next to the column letters and the row number for G2 and G3. We haven’t locked the A2 column as it needs to change for each person we calculate. Our formula should look like this:

= (A2: $ G $ 2) / $ G $ 3

After locking our cells, we can click the lower right corner of the B2 cell and drag it onto B11. Now we have z-values ​​for our entire data set.

Step 5: standardize the formula for the z-score

It was the manual method for calculating the z-score. You can also use the STANDARDIZE formula to determine it. Suppose we are using a similar data set. 90,72,87, 65, 50, 78, 82, 63, 55, and 80 from cell A2 to cell A11. Our mean appears in the G2 cell, and the standard deviation value appears in the G3 cell.

We formed two “Z-Score” columns at the beginning. Regardless of which column you filled in, it would help if you filled in the blank column next to it. In our practice, it’s the column with the letter C. We click on an empty C2 cell and enter the STANDARDIZE formula in the formula area above our sheet:

= STANDARDIZE (A2, G2, G3)

We hit Enter, and the value is the same as in our other Z-Score column. It’s -0.01476. We can do this for the entire dataset in the same way as before. We lock the middle cell and the standard deviation value cell by adding the dollar sign ($) next to the column letter and row number. Again, we’re not going to lock the data point cell as we want it to change for each cell. Now our formula should look like this:

= STANDARDIZE (A2, $ G $ 2, $ G $ 3)

After locking our cells, we can click the lower right corner of the C2 cell and drag it onto C11. Now we have z-values ​​for our entire data set.