Purpose
Return value
Syntax
=STANDARDIZE(x,mean,standard_dev)- x - The value to normalize.
- mean - The arithmetic mean of the distribution.
- standard_dev - The standard deviation of the distribution.
How to use
The Excel STANDARDIZE function returns a normalized value (z-score) based on the mean and standard deviation. To use the STANDARDIZE function, calculate the mean with the AVERAGE function, and the standard deviation with the STDEV.P function (see below).
In the example shown, the formula in D5 is:
=STANDARDIZE(C5,$G$4,$G$5)
About z-scores / standard scores
A z-score, or standard score, is a way of standardizing scores on the same scale by dividing a score's deviation by the standard deviation in a data set. The result is a standard score, or a z-score. It measures the number of standard deviations a given data point is from the mean.
A z-score can be negative or positive. A negative z-score indicates a value less than the mean, and a positive z-score indicates a value greater than the mean. The average of every z-score for a data set is zero.
To calculate a z-score, you need to calculate the mean and standard deviation. The formulas in G4 and G5 are, respectively:
=AVERAGE(points)
=STDEV.P(points)
Where "points" is the named range C5:C12.
If you'd like to learn more z-scores, and statistical analysis in Excel, I recommend Joseph Schmuller's book Statistical Analysis with Excel For Dummies
Related functions
SMALL Function
The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
RANK Function
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time).
RANK.AVG Function
The Excel RANK.AVG function returns the rank of a number against a list of other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates....
STDEV.S Function
The Excel STDEV.S function calculates the standard deviation for a sample set of data. STDEV.S replaces the older STDEV function, with the same behavior.
STDEV.P Function
The Excel STDEV.P function calculates the standard deviation for a sample set of data. STDEV.P calculates standard deviation using the "n" method, ignoring logical values and text.
