It simply adds up all the values in a cell range and divides that sum by the number of cells that contain numeric values (ignore blank cells). The first statistical value you need is the “mean” and Excel’s “AVERAGE” function calculates that value. Comparing the Z-scores of the two students might reveal that the student scoring 87% did better compared to the rest of his class than the student scoring 98% compared to the rest of his class.

You know that the first student got 95% on the final exam in one class and the student in the other class got 87%.Īt first glance, the 95% grade is more impressive, but what if the teacher in the second class gave a more difficult test? You can calculate the Z-score of each student’s score based on the mean scores for each class and the standard deviation of the scores for each class. Suppose you want to compare the test results of two algebra students taught by different teachers. =(DataPoint-AVERAGE(DataSet))/STDEV(DataSet) It is defined as the number of standard deviations from the mean within which a data point lies. What is a Z-Score and what do the AVERAGE, STDEV.S, and STDEV.P functions do?Ī Z-Score is a simple way to compare values from two different data sets. You can use the AVERAGE and SSTDEV or PSTDEV formulas to calculate the mean and standard deviation of your data, and then use those results to determine the Z-score for each value. How to Calculate a Z-Score Using Microsoft ExcelĪ Z-Score is a statistical value that tells you how many standard deviations a particular value is from the mean of the entire data set.

How to use microsoft excel to calculate