How to Find Critical Z Score in Excel
If you are familiar with statistics, you might have heard of the term, "Z-Score". In statistics, Z-Score is the number of standard deviations above or below a data point.
Excel provides the ability to calculate Z-Score, and it's fairly straightforward. It is important to note that Z-Score requires a data point, mean value, and standard deviation value. Without these, the calculation of Z-Score isn't possible.
In this article, we will show you how to calculate the Z-Score function in Excel.
How to Calculate a Z-Score in Excel
To calculate Z-Score in Excel, you need to understand how Z-Score works in general through Statistics.
The formula that is used to calculate Z-Score is Z=(x-µ)/σ, where the arguments are:
- Z = Z score value.
- X = The value that needs to be standardized.
- µ = Mean of the given set of data values.
- σ = Standard deviation of the given set of data values.
Simply put, Z-Score is how you measure a number's standard deviation above or below the given data point. To calculate it, you must take the data point. Next, subtract the mean value from it, and finally divide it by the standard deviation value.
To calculate the Z-Score, you need a set of data that can provide you with the mean and standard deviation for the firstmost variable.
- To calculate the mean of any given range, the syntax is: =AVERAGE(range)
- Similarly, to calculate the Standard deviation, the syntax is: =STDEVPA(range)
Example for Calculating Z-Score in Excel
Let's illustrate the calculation of the Z-Score using an example in an Excel spreadsheet.
Here we have a set of data that resembles the sales of some fruits. The names of the fruits are depicted in the A column from A1:A7; while their sales are present in the B column from B1:B7. We will calculate the Z-Score for each value in the C column next to the respective sales value.
Before calculating the Z-Score, let us calculate the Mean and Standard Deviation in the B9 and B10 cells, respectively.
To calculate the mean of the given data, we use the AVERAGE function in Excel. Since our range is from the B2:B7, we'll use the condition: =AVERAGE(B2:B7). Hence, the mean value in our example is 173.3333
Similarly, we would need to calculate the Standard Deviation of the same range. So the function for Standard deviation, in our case, will be =STDEVPA(B2:B7). Thus, the standard deviation for our example is 51.51267
Now we have to calculate the Z-Score of each value. The Z-Score results will be displayed in the C column, next to the respective values in the B column.
To calculate the Z-Score, we subtract the mean value from the data point and then divide the result by the standard deviation. You might find it confusing at first, but the example will clarify things.
In the spreadsheet, let's calculate the Z-Score of the sales of oranges, which is 122. So the Z-Score will be =(B2-B9)/B10. B2 is the number of sales, B9 is the mean of the range, and B10 is the standard deviation here.
As a result, the Z-Score of the first value is -0.99652. Similarly, if we continue the same with the B3, B4, B5, B6, and B7 cells, we'll get their respective Z-Scores. In our case, they are 1.235942, -0.91887, 0.964164, 0.770037, -1.05476.
So, this is how you calculate the Z-Score of any given range.
Automate Your Spreadsheets With Z-Score Function in Excel
In conclusion, Z-Score involves some mathematics and is quite straightforward to understand. But if you don't want to get into complicated mathematics, it's best to go with the SUMIF function.
How to Use the SUMIF Function in Excel
SUMIF is a built-in function in Excel that is categorized as a Math/Trig function. Here's how you can use it!
Read Next
About The Author 
Varun Kesari (41 Articles Published)
Varun Kesari is the Junior Editor at MakeUseOf for the Office & Productivity and Work & Career sections. His career in tech journalism began in 2015, and he has written and edited for various well-established publications. He is passionate about all things related to technology. You can reach out to him: varunkesari@makeuseof.com
More From Varun Kesari
How to Find Critical Z Score in Excel
Source: https://www.makeuseof.com/how-to-calculate-z-score-excel/
0 Response to "How to Find Critical Z Score in Excel"
Post a Comment