The `STANDARDIZE` function in Excel is used to calculate the standardized value (also known as a z-score) of a data point. This function allows you to see how far away a particular value is from the mean in terms of standard deviations. The formula for z-score is:
[ z = frac{x – mu}{sigma} ]
Where:
- ( x ) is the value to be standardized,
- ( mu ) is the mean of the data set,
- ( sigma ) is the standard deviation of the data set.
Syntax
STANDARDIZE(x, mean, standard_dev)
- x: The data point you want to standardize.
- mean: The arithmetic mean of the distribution.
- standard_dev: The standard deviation of the distribution.
Example
Suppose you have the following data set and you want to standardize the value in column A:
| Value (A) |
|———–|
| 10 |
| 12 |
| 15 |
| 18 |
| 20 |
=STANDARDIZE(A1, AVERAGE(A$1:A$5), STDEV.P(A$1:A$5))
- Calculate the mean and standard deviation for your data set. You can use the `AVERAGE` and `STDEV.P` (for population data) or `STDEV.S` (for sample data) functions:
- Mean: `=AVERAGE(A1:A5)`
- Standard Deviation (for population): `=STDEV.P(A1:A5)`
- Use the `STANDARDIZE` function to calculate the standardized value for a data point. Let’s calculate the standardized value for the first data point (10):
- Drag the formula down for other cells to calculate standardized values for the entire column:
| Value (A) | Standardized Value |
|———–|———————|
| 10 | -1.26 |
| 12 | -0.75 |
| 15 | -0.03 |
| 18 | 0.69 |
| 20 | 1.35 |
Each value in the “Standardized Value” column represents how many standard deviations the original value is from the mean.
Tips
- Ensure that your “mean” and “standard_dev” arguments are calculated using the same data set that contains the value “x” you are standardizing.
- Z-scores can be helpful for comparing data points from different data sets or detecting outliers.
This function is particularly useful in statistical analysis, normalizing data before analysis, or when comparing scores from different scale measurements.
Unlock Your Potential
Excel
Access
Power BI
