STANDARDIZE function

The STANDARDIZE function in Excel is used to calculate the standard score (also known as a z-score) of a value in a dataset. This function helps to determine how many standard deviations a data point is away from the mean of the dataset.

Syntax

=STANDARDIZE(x, mean, standard_dev)

Parameters

1. x (required): The value for which you want to calculate the standard score (z-score).
2. mean (required): The arithmetic mean of the dataset.
3. standard_dev (required): The standard deviation of the dataset.

Formula

The formula for calculating the standard score (z-score) is:

$$z = \frac{x – \mu}{\sigma}$$

Where:

• $x$ is the value you are standardizing.
• $\mu$ is the mean of the dataset.
• $\sigma$ is the standard deviation of the dataset.

This result indicates how far and in which direction the value $x$ is from the mean, expressed in terms of standard deviations.

Key Points

• The result tells you how many standard deviations a value is from the mean. A positive result means the value is above the mean, while a negative result means the value is below the mean.
• Standardizing data helps to compare values from different datasets or distributions that may have different scales or units.

Examples

1. Basic Standardization

Given the dataset {5, 10, 15, 20, 25} in A1:A5, where:

• Mean = 15
• Standard deviation = 7.91 (approx.)

To calculate the z-score for x = 10:

=STANDARDIZE(10, 15, 7.91)

Result: -0.632

• The value 10 is approximately 0.632 standard deviations below the mean of 15.

2. Standardize with Larger Dataset

For the dataset {50, 60, 70, 80, 90, 100} in B1:B6, where:

• Mean = 75
• Standard deviation = 18.26 (approx.)

To calculate the z-score for x = 60:

=STANDARDIZE(60, 75, 18.26)

Result: -0.82

• The value 60 is approximately 0.82 standard deviations below the mean of 75.

3. Standardization with Negative Values

For the dataset {−5, −3, −1, 1, 3, 5} in C1:C6, where:

• Mean = 0
• Standard deviation = 3.16 (approx.)

To calculate the z-score for x = −3:

=STANDARDIZE(-3, 0, 3.16)

Result: -0.949

• The value −3 is approximately 0.949 standard deviations below the mean of 0.

4. Standardizing Multiple Values

To standardize a range of values, such as D1:D5 containing {20, 25, 30, 35, 40}, where:

• Mean = 30
• Standard deviation = 7.91 (approx.)

Use the STANDARDIZE function in a column:

=STANDARDIZE(D1, 30, 7.91)

Drag the formula down to standardize the other values in the range.

Notes

• Error Handling:
  • If standard_dev is 0, the function will return a #DIV/0! error because division by zero is undefined.
  • Non-numeric values in the parameters will result in a #VALUE! error.
• Use Cases:
  • Comparing Values: Standardization helps compare values from different datasets or distributions, as it removes the units and scales the data to a common scale.
  • Normalization: This function is often used in data normalization processes to prepare datasets for machine learning or statistical analysis.
  • Identifying Outliers: Values with z-scores greater than 3 or less than -3 are typically considered outliers.

Related Functions

• AVERAGE: Returns the mean of a dataset.
• STDEV.P / STDEV.S: Returns the standard deviation of a population or sample dataset.
• Z.TEST: Performs a z-test to determine if a value is significantly different from the population mean.
• NORM.S.DIST: Returns the cumulative distribution function (CDF) of the standard normal distribution for a z-score.

The STANDARDIZE function is highly useful in statistics and data analysis, particularly when comparing values across different distributions or ensuring that data fits a standard scale for further processing or analysis.