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.
Using the STANDARDIZE function
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.
Purpose
Return value
Syntax
=STDEV(number1,[number2],...)
- number1 - First number or reference in the sample.
- number2 - [optional] Second number or reference.
Using the STDEV function
The STDEV function calculates the standard deviation for a sample set of data. Standard deviation measures how much variance there is in a set of numbers compared to the average (mean) of the numbers. The STDEV function is meant to estimate standard deviation in a sample. If data represents an entire population, use the STDEVP function.
In the example shown, the formula in F7 is:
=STDEV(C5:C11)
Note: Microsoft classifies STDEV as a " compatibility function “, now replaced by the STDEV.S function .
Standard Deviation functions in Excel
The table below summarizes the standard deviation functions provided by Excel.
| Name | Data set | Text and logicals |
|---|---|---|
| STDEV | Sample | Ignored |
| STDEVP | Population | Ignored |
| STDEV.S | Sample | Ignored |
| STDEV.P | Population | Ignored |
| STDEVA | Sample | Evaluated |
| STDEVPA | Population | Evaluated |
Notes:
- STDEV calculates standard deviation using the “n-1” method.
- STDEV assumes data is a sample only. When data represents an entire population, use STDEVP or STDEV.P.
- Numbers are supplied as arguments. They can be supplied as actual numbers, ranges, arrays, or references that contain numbers.
- STDEV ignores text and logical values that occur in references, but evaluates text and logicals hardcoded as function arguments.
- To evaluate logical values and/or text in the calculation, use the STDEVA function .
