Last updated on October 23rd, 2024 at 03:58 pm
The term “Z-Score” may be familiar to you if you are familiar with statistics. The number of standard deviations up or below a data point is known as the Z-Score in statistics.
The Z-Score can be calculated in Excel, and it’s a fairly simple process. It is significant to remember that a data point, mean value, and standard deviation value are prerequisites for Z-Score. The Z-Score cannot be calculated without these. We’ll demonstrate how to calculate the Z-Score function in Excel to make things easier for you.
What is Z score
The relationship between the dataset values and it’s specified data is determined using the fundamental statistical calculation known as the Z score.
The Z score represents where a raw score from the mean value of the data set falls in the units of standard deviation. The Z score not only identifies an element’s degree of deviation but also pinpoints the precise location of the raw score in relation to the mean. In simple words, it is a calculation of how far a raw element deviates from the mean by how many standard deviations.
By standardizing the distribution, the Z score is utilized to compare the results of various types of variables. The standard score is another name for it. The population can be standardized using it. A standard normal distribution with a mean of 0 and a standard deviation of 1 can fit the Z score.
What values can Z score take?
- If the Z score is zero, the raw score and mean score are both equal. This means that the score deviates from the mean by 0 standard deviations.
- The raw score is said to be above the mean value and to the right of the mean in the SND graph if the Z score is positive.
- Negative shows that the raw score is to the left of the mean in the SND graph, below the mean value.
The Z score typically falls between three standard deviations below and above the mean.
We need to know the data set’s mean and standard deviation in order to calculate the Z score.
By adding all the elements together and dividing the result by the total number of elements, the mean can be calculated.
Where
- X = the elements
- N = number of elements
The average value is defined as the mean value of a given set of data. It is calculated by adding the observations together and then dividing by the overall sample size. The mean is the midpoint of a range of values.
The mean of the squared departures of each component from the mean makes up the standard deviation.
Standard Deviation Versus Z score?
The standard deviation is equal to the variance squared. As a result, it is a measurement of the dataset’s spread in relation to the mean. The sigma symbol (σ) is used to symbolize it.
An easy method of contrasting values from two different data sets is to use a Z-Score. It is described as the amount of standard deviations a data point deviates from the mean. The general formula appears as follows:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
A clarification example is provided below. Imagine you wanted to compare the test scores of two students taking Algebra from different instructors. You are aware that the first student in one class received a 95% on the final exam, while the second student received an 87%.
At first glance, the 95% grade seems more remarkable, but what if the instructor of the second class gave an exam that was harder? You may compute the Z-Score for each student’s score using the general scores in every class and the basic deviation of the scores in every class.
A comparison of the two students’ Z-Scores revealed that the student with the 87% score outperformed the student with the 98% score in relation to the rest of their class.
The “AVERAGE” function in Excel determines the “mean,” which is the first statistical value you require. It merely adds up all of the values in a cell range, divides that total by the number of cells that have numerical values, and returns the result (it ignores blank cells).
The other statistic we require is the “standard deviation,” which can be calculated using two different Excel functions in two slightly different ways.
The “STDEV” function, which computes the standard deviation while treating the data as a “sample” of a population, was the only one available in earlier versions of Excel. Excel 2010 divided that into two standard deviation calculation functions:
STDEV.S: This function and the previous “STDEV” function are the same. The standard deviation is calculated using the data as a “sample” of the population. An example of a sample of a population would be the specific mosquitoes gathered for a study or the cars set aside and used for crash safety testing.
STDEV.P: Using the data as the entire population, this function determines the standard deviation. An example of an entire population would be all the mosquitoes on the planet or all the vehicles in a particular model’s production run.
Based on your data set, you decide which. The STDEV.P function’s result will always be less than the STDEV.S function’s result for the same data set, though the difference will typically be negligible. Assuming there is more variability in the data is a more cautious approach.
Excel Z-Score Calculation Instructions
You must have a general understanding of statistics to calculate Z-Score in Excel. Z=(x-µ)/σ is the formula used to calculate Z-Score, with the following arguments:
- Z = Z is the score value.
- X = The value that has to be standardized.
- µ = Mean of the set of data values given.
- σ = Standard deviation of the set of data values given.
Z-Score, in its simplest form, is a way to quantify a number’s standard deviation above or below the specified data point. You must use the data point to calculate it. Divide it by the standard deviation value after deducting the mean value from it.
You need a data set that can give you the mean and standard deviation for the first most variable in order to calculate the Z-Score.
- To calculate the mean of any range given, the syntax is: =AVERAGE(range)
- Identical to that, the Standard deviation calculation is done by, keeping the syntax: =STDEVPA(range)
An Example of a Z-Score Calculation in Excel
Let’s use an Excel spreadsheet example to demonstrate how to calculate the Z-Score.
A set of data that closely resembles the sales of some fruits is presented here. The fruit names and their sales are shown from B1 to B7. For every value in the C column next to the corresponding sales value, the Z-Score will be determined.
Let’s first compute the mean and standard deviation in the cells before computing the Z-Score.
The AVERAGE function in Excel is used to determine the mean of the provided data. We’ll use the condition =AVERAGE because our range is from B2:B7 (B2:B7). Therefore, in our example, the mean value is 173.333333.
In a similar manner, we would have to figure out the same range’s Standard Deviation. Therefore, in our case, the standard deviation function will be =STDEVPA (B2:B7). Therefore, 51.5126737 is the standard deviation for our example.
We must now determine the Z-Score for each value. Next to the corresponding values, the Z-Score results will be shown.
The mean value is subtracted from each data point to determine the Z-Score, which is then calculated by dividing the result by the standard deviation. At first, it might seem unclear, but the example will make everything clear.
Let’s determine the orange sales’ Z-Score in the spreadsheet, which is 122. The Z-Score will therefore be =(B2-B9)/B10. Here, B2 represents the number of sales, B9 is the range’s mean, and B10 is the standard deviation.
As a result, the first value’s Z-Score is -0.9965815. The Z-Scores for the B3, B4, B5, B6, and B7 cells will be obtained if we repeat the process in a similar way. They are 1.2359418, -0.9188677, 0.96416402, 0.77003704, and -1.0547566 in our instance.
Thus, this is how the Z-Score of any given range is determined. Similar to this, if you’re working with a lot of data in a spreadsheet and you need to look for something in the range or the table, you can use Excel’s XLOOKUP function.
Z-Score calculation without using “Helper” Cells
Helper cells, such as those that store the outcomes of the “AVERAGE,” “STDEV.S,” and “STDEV.P” functions, store a result. Although they are not always required, they can be useful. Use the following generalized formulas to calculate a Z-Score instead of them to completely avoid them.
Using the “STDEV.S” function, here is an example:
=(Value-AVERAGE(Values))/STDEV.S(Values)
And one using the “STEV.P” function:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Make sure to include absolute references (“$” using F4) when entering cell ranges for the “Values” in the functions so that when you “fill-down,” you aren’t computing the standard or average deviation of a unique range of cells in each formula.
The use of helper cells may be more effective if you have a large amount of data because it avoids calculating the results of the “AVERAGE” and “STDEV.S” or “STDEV.P” functions each time, conserving processor resources and accelerating the calculation of the results.
Additionally, “$G$3” loads from RAM faster than “AVERAGE($E$3:$E$12)” and requires fewer bytes to store. This is significant because Excel’s default 32-bit version only supports 2GB of RAM (the 64-bit version does not have any limitations on how much RAM can be used).
Conclusion
So this way we know the formula to calculate the Z score and along with that how you can do it yourself. We hope that you have understood it thoroughly.
