STANDARDIZE Function (LibreOffice Calc)
The STANDARDIZE function in LibreOffice Calc converts a value into a z-score based on a given mean and standard deviation. This guide explains syntax, interpretation, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the STANDARDIZE Function Does â–¾
- Converts a value into a z-score
- Measures how far a value is from the mean in standard deviation units
- Useful for normalization, probability, and statistical testing
- Works across sheets
Z-score formula:
[ z = \frac{x - \mu}{\sigma} ]
Syntax â–¾
STANDARDIZE(x; mean; standard_dev)
Where:
x— the value to standardizemean— the distribution meanstandard_dev— the distribution standard deviation
standard_dev must be positive.
Interpretation of Z-Scores â–¾
| Z-score | Meaning |
|---|---|
| 0 | Exactly average |
| +1 | One standard deviation above mean |
| -1 | One standard deviation below mean |
| +2 / -2 | Unusual values |
| +3 / -3 | Rare/extreme values |
Z-scores are unitless and comparable across datasets.
Basic Examples â–¾
Standardize a value
=STANDARDIZE(85; 70; 10)
Standardize a cell value
=STANDARDIZE(A2; AVERAGE(A1:A100); STDEV.S(A1:A100))
Standardize across sheets
=STANDARDIZE(Sheet1.A1; Sheet2.B1; Sheet2.C1)
Standardize a date (converted to serial number)
=STANDARDIZE(A1; AVERAGE(A1:A100); STDEV.S(A1:A100))
Advanced Examples â–¾
Standardize an entire dataset (spill array)
=STANDARDIZE(A1:A100; AVERAGE(A1:A100); STDEV.S(A1:A100))
Standardize ignoring errors
=STANDARDIZE(A2; AVERAGE(IF(ISNUMBER(A1:A100); A1:A100)); STDEV.S(IF(ISNUMBER(A1:A100); A1:A100)))
(Confirm with Ctrl+Shift+Enter in older Calc.)
Standardize filtered (visible) data only
Use SUBTOTAL helper column to filter values before computing mean and SD.
Standardize after removing outliers
=STANDARDIZE(A2; AVERAGE(FILTER(A1:A100; A1:A100<1000)); STDEV.S(FILTER(A1:A100; A1:A100<1000)))
Standardize for probability calculations
=NORM.DIST(STANDARDIZE(A2; Mean; SD); 0; 1; TRUE)
Standardize for machine learning preprocessing
=STANDARDIZE(A2; Mean; SD)
Standardize for z-test preparation
=STANDARDIZE(SampleValue; PopulationMean; PopulationSD)
How STANDARDIZE Calculates Z-Scores â–¾
The formula is:
[ z = \frac{x - \mu}{\sigma} ]
Where:
- ( x ) = raw value
- ( \mu ) = mean
- ( \sigma ) = standard deviation
This transformation converts any distribution into a standard normal scale.
Common Errors and Fixes â–¾
Err:502 — Invalid argument
Occurs when:
- Standard deviation ≤ 0
- Mean or SD is non-numeric
- x is non-numeric
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
Z-score seems incorrect
Possible causes:
- Wrong mean or SD used
- Using STDEV.P vs STDEV.S incorrectly
- Outliers inflating SD
- Data not cleaned before standardization
Best Practices â–¾
- Use STANDARDIZE for z-scores, normalization, and probability work
- Use STDEV.S for sample SD and STDEV.P for population SD
- Remove outliers before standardizing
- Use named ranges for cleaner formulas
- Use NORM.DIST and NORM.INV for probability calculations
- Use STANDARDIZE before clustering or ML preprocessing
STANDARDIZE transforms raw values into a universal scale — perfect for comparing scores, detecting outliers, and preparing data for statistical modeling.