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. To simplify things for you, we’ll 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 first most 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 a 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.333333.
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.5126737.
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.9965815. 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.2359418, -0.9188677, 0.96416402, 0.77003704, -1.0547566.
So, this is how you calculate the Z-Score of any given range. Similarly, if you’re working with a lot of data in a spreadsheet, you can use the XLOOKUP function in Excel when you need to find something in a table or range.
Automate Your Spreadsheets With the 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.
