STANDARDIZE function
STANDARDIZE returns the z-score of a value, meaning how many standard deviations it sits from the mean. It puts values from different scales on the same footing, such as two exam scores.
Example
Read it as a sentence: turn the value in D1 into a z-score using a mean of 40 and a standard deviation of 1.5, which gives -26.66666667.
Excel shows -26.66666667
D
1
Data
Syntax
STANDARDIZE(x, mean, standard_dev)
-
x(required) - The value to convert.
-
mean(required) - Average of the distribution.
-
standard_dev(required) - Spread of the distribution; must be positive.
Returns: The z-score: how many standard deviations x is from the mean.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=STANDARDIZE(42,40,1.5) | 1.333333333 | Working example |
=STANDARDIZE("42",40,1.5) | 1.333333333 | Text-number in first arg |
=STANDARDIZE({42,42},40,1.5) | 1.333333333 | Array in first arg (lifts, spills) |
=STANDARDIZE(Data!D1,40,1.5) | -26.66666667 | Blank cell as first arg |
=STANDARDIZE("abc",40,1.5) | #VALUE! | Non-numeric text in first arg |
=STANDARDIZE(NA(),40,1.5) | #N/A | Error in first arg |
=STANDARDIZE(42,40,0) | #NUM! | Sd 0 |
=STANDARDIZE(42,40,-1) | #NUM! | Negative sd |
The Data sheet these formulas read
A cell that starts with = holds that formula.
D
1
Data
Watch out for
- standard_dev of 0 or less gives #NUM!.
- It simply computes (x - mean) / standard_dev.
Where it works
- Excel
- STANDARDIZE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .