Check Sheet Logo
Check Sheet

STANDARDIZE

The STANDARDIZE function is a statistical function used to standardize a given value based on a specified mean and standard deviation. This is useful for comparing values that are measured on different scales. The function returns a z-score, which represents the number of standard deviations a value is from the mean. The formula used is: (value - mean) / standard_deviation.

Usage

Use the STANDARDIZE formula with the syntax shown below, it has 3 required parameters:

=STANDARDIZE(value, mean, standard_deviation)
Parameters:
  1. value (required):
    The value to be standardized.
  2. mean (required):
    The mean value to use as the basis for standardization.
  3. standard_deviation (required):
    The standard deviation to use as the basis for standardization. Must be greater than zero.

Examples

Here are a few example use cases that explain how to use the STANDARDIZE formula in Google Sheets.

Comparing values from different datasets

Suppose you have two datasets with different scales and you want to compare the values in a meaningful way. By using the STANDARDIZE function, you can standardize the values based on their respective means and standard deviations, and then compare the resulting z-scores.

Identifying outliers

In a dataset, an outlier is a value that is significantly different from the other values. The STANDARDIZE function can be used to identify outliers by calculating the z-score for each value. Values with a z-score greater than a certain threshold (e.g. 3 or -3) are considered outliers.

Normalizing data

To normalize data is to transform it into a standard format. With the STANDARDIZE function, you can normalize a dataset by standardizing all the values based on a common mean and standard deviation. This is useful for machine learning and other statistical applications that require normalized data.

Common Mistakes

STANDARDIZE not working? Here are some common mistakes people make when using the STANDARDIZE Google Sheets Formula:

Forgetting to include all three parameters

The STANDARDIZE formula requires all three parameters to be included. Forgetting to include any of them will result in an error.

Dividing by zero

If the standard_deviation parameter is zero, the STANDARDIZE formula will result in a divide by zero error. Make sure that the standard_deviation parameter is not zero.

Related Formulas

The following functions are similar to STANDARDIZE or are often used with it in a formula:

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

  • STDEV

    The STDEV function calculates the standard deviation of a set of numbers. It measures the amount of variation or dispersion of a set of values from the average (mean) value. It is commonly used in statistics to determine the spread of a data set. The values can be supplied as individual cells, ranges, or constants.

  • ZTEST

    The ZTEST function in Google Sheets returns the one-tailed probability-value of a z-test. It is used to determine whether a sample mean is significantly different from a hypothesized value, assuming a known population standard deviation. This function is typically used in hypothesis testing and statistical analysis.

Learn More

You can learn more about the STANDARDIZE Google Sheets function on Google Support.