Contact
12 programs
Explore the site

STANDARDIZE Function (LibreOffice Calc)

Math Beginner LibreOffice Calc Introduction not established

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.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
  • Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
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.

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.