Browse

STANDARDIZE Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 3.0

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.

The **STANDARDIZE** function converts a raw value into a **z-score**, showing how many standard deviations it lies above or below the mean. Z-scores are essential for probability calculations, outlier detection, and statistical modeling.

This guide provides syntax, interpretation rules, examples, common errors, and best practices, making it one of the most complete STANDARDIZE references available for LibreOffice Calc.

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 standardize
  • mean — the distribution mean
  • standard_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.