Exceljet

Quick, clean, and to the point

Excel STANDARDIZE Function

Excel STANDARDIZE function
Summary 

The Excel STANDARDIZE function returns a normalized value (z-score) based on the mean and standard deviation.

Purpose 
Calculate a normalized value (z-score)
Return value 
Normalized value
Syntax 
=STANDARDIZE (x, mean, standard_dev)
Arguments 
  • x - The value to normalize.
  • mean - The arithmetic mean of the distribution.
  • standard_dev - The standard deviation of the distribution.
Usage notes 

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 

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.