Excel has changed the way people collect, organize, and analyze data. It enables the optimistic organization of massive amounts of data in an infinite number of dimensions. Excel allows for data management through large spreadsheets. This Excel spreadsheet has 256 columns and 16,384 rows, allowing for large data sheets to be accommodated. These data sheets can be updated and edited as needed.
Aside from data collection, organization, and presentation, it also improves data analysis. This data analysis consists of a variety of data operations that can be performed on data units to obtain additional information from them. To perform these operations, a set of defined formulas is inserted into the cells. This article helps you in making your Excel Homework Help. And if you are looking for assignment help, Our experts will help you.
This article is designed to allow users to know how to calculate Z scores in an excel dataset. Conventionally there are two ways to find out Z score in the excel spreadsheets. One is the direct method to calculate Z score, while the other is through an indirect route. Both the ways are detailed step by step in this article.
STEPS TO FIND Z SCORE IN EXCEL
-
INDIRECT METHOD
The indirect method to calculate Z score involves calculation of average using the formula for average in excel, calculation standard deviation through the direct formula for standard deviation given in excel formulas section and then applying these two things for calculating Z score.
-
Calculating average
The first step for calculating the Z score for data vale is the calculation of Average using the formula given for calculating average in excel. This requires selecting the AVERAGE function from the formulas tab. After selecting the formula, one has to enter the range of cells for which the average value is to be calculated (AVERAGE A2:A11). This produces the average value for the selected dataset. After obtaining the average value for the selected dataset, as depicted in the given picture, the next step is calculation of standard deviation.
-
Finding out standard deviation
The next step in the process of calculating the Z score is the calculation of standard deviation. For calculating the standard deviation, the function STDEV is to be selected from the formulas tab. After selecting the formula for standard deviation, one has to enter the range of data values for which the standard deviation has to be selected. Here in the picture given below, the value for standard deviation is calculated using the formula: STDEV A2:A11.
This formula will obtain the value of standard deviation for data values in the cell numbers from A2 to A11. The obtained value here is 7.1655 approx.
-
Finding out individual z score by entering a formula
After collecting the necessary data for the determination of the Z score for the given values, the next step is entering the formula for the final calculation of the Z score. The formula to obtain the Z score for the given values of mean and standard deviation is (A2-D1)/D3. Here A2 represents the value in cell number 2 in column A. D1 is the data value for the average of the dataset, and D3 is the data value for the standard deviation for the dataset.
On executing this formula, Excel will produce the desired value of Z score in cell B2, for the concerned raw value in the mentioned cell. This operation has to be repeated a desired number of times. To obtain the Z scores for other values in the dataset, the operators are required to enter the above-mentioned formula for z score in the column for Z score, against the concerned value of raw data.
-
DIRECT METHOD
Excel also has a direct formula for calculating Z score. It applies the STANDARDIZE formula to a given set of values to obtain the value for the Z score.
For obtaining the Z score through this method, one has to select the STANDARDISE function from the formulas section. After selecting the standardized option, one has to enter the cell numbers where the data values for average, standard deviation, and the raw number are stored. In this method excel itself operates on the given data values. Here the user does not have to enter the entire formula repeatedly for every upcoming value, they can simply select the STANDARDIZE formula and simply enter the cell numbers for mean, standard deviation, and the raw data value.
The formula for calculating the Z score is written as STANDARDISE (A2, D1, D3). Using this given formula one can find out the Z score for a given data value without much effort.
CONCLUSION
A Z score is a data value that depicts the mutual relationship between the mean value for a greater dataset and an individual value from the same dataset. It usually determines the variable nature of data and is helpful in the decision making process. The above methods to calculate Z score facilitate the calculation of z scores in excel documents with greater efficiency and simple techniques. After going through the above article, learners can conveniently calculate Z scores in Excel within no time.
