STANDARDIZEfunction 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.
- How to use
- Examples of using
STANDARDIZEformula not working?
- Similar formulas to
STANDARDIZE formula with the syntax shown below, it has 3 required parameters:
- value (required):
The value to be standardized.
- mean (required):
The mean value to use as the basis for standardization.
- standard_deviation (required):
The standard deviation to use as the basis for standardization. Must be greater than zero.
ExamplesHere are a few example use cases that explain how to use the
STANDARDIZEformula 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.
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.
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.
STANDARDIZEnot working? Here are some common mistakes people make when using the
STANDARDIZEGoogle Sheets Formula:
Forgetting to include all three parameters
STANDARDIZE formula requires all three parameters to be included. Forgetting to include any of them will result in an error.
Dividing by zero
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.
The following functions are similar to
STANDARDIZE or are often used with it in a formula:
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.
STDEVfunction 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.
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.
You can learn more about the
STANDARDIZE Google Sheets function on Google Support.