How to Use the Standardize Function in Excel
In this Excel tutorial I show how to use Standardize Function in Excel. The Standardize function transforms a value within a dataset into its corresponding z-score, which tells you how many standard deviations that value is away from the mean of the dataset. This is useful for comparing data points from different distributions or for identifying outliers. Let’s break down how to use the Standardize function in Excel.
Understanding the Basics
The Standardize function has a simple syntax =STANDARDIZE(x, mean, standard_dev)
- x: This is the value you want to standardize. It’s the data point you’re analyzing.
- mean: This is the average of the dataset that x belongs to.
- standard_dev: This is the standard deviation of the dataset. It measures the spread or dispersion of the data.
How Standardize Works
The function calculates the z-score using the following formula z = (x – mean) / standard_dev
Essentially, it subtracts the mean from your value (x) to find the difference, and then divides that difference by the standard deviation. This gives you a standardized value that represents how many standard deviations above or below the mean your original value was.
Practical Examples
Let’s say you have test scores for two different classes. One student scored 80 in the first class, which had a mean of 70 and a standard deviation of 10. Another student scored 75 in the second class, which had a mean of 72 and a standard deviation of 5. Which student performed better relative to their class?
- For the first student: =STANDARDIZE(80, 70, 10) returns 1.
- For the second student: =STANDARDIZE(75, 72, 5) returns 0.6.
The first student has a higher z-score, meaning they performed better relative to their class, even though their raw score was only slightly higher than the second student’s.
Using Standardize in Practice
- Before using Standardize , you need to calculate the mean and standard deviation of your dataset. You can use the Average and Stdev.s (for sample standard deviation) or Stdev.p (for population standard deviation) functions in Excel for this.
- Once you have the mean and standard deviation, you can use the Standardize function. Replace x, mean, and standard_dev with the appropriate cell references or values.
- The result of the Standardize function is the z-score. A positive z-score means the value is above the mean, and a negative z-score means it’s below the mean. The larger the absolute value of the z-score, the further the value is from the mean.
The Standardize function assumes that the data is normally distributed. While it can still be used with non-normally distributed data, the interpretation of the z-scores might be less accurate.
