Browse

PERCENTILE Function (OpenOffice Calc)

Statistical Intermediate OpenOffice Calc OpenOffice.org 3.0

The PERCENTILE function in OpenOffice Calc returns the value at a specified percentile of a dataset. Learn syntax, interpolation rules, examples, common errors, and best practices.

The PERCENTILE function in OpenOffice Calc returns the value at a specified percentile of a dataset. Percentiles are essential for statistical analysis, grading systems, distribution modeling, and performance benchmarking.

This guide provides syntax, interpolation rules, examples, advanced techniques, common errors, and best practices, making it one of the most complete PERCENTILE references available for OpenOffice Calc.

What the PERCENTILE Function Does

  • Returns the value at a given percentile
  • Uses linear interpolation when needed
  • Accepts percentiles from 0 to 1
  • Useful for distribution analysis, grading, and benchmarking
  • Works across sheets

PERCENTILE is ideal when you need to understand relative position within a dataset.

Syntax

PERCENTILE(range; k)

Arguments:

  • range — The dataset
  • k — The percentile (0 to 1)

Examples of valid percentiles:

  • 0 → minimum
  • 0.25 → 25th percentile
  • 0.5 → median
  • 0.9 → 90th percentile
  • 1 → maximum
OpenOffice Calc uses inclusive percentile calculation (equivalent to Excel’s PERCENTILE.INC).

How Percentile Interpolation Works

If the percentile falls between two data points, Calc uses linear interpolation:

[ x = x_{\text{lower}} + (fraction \cdot (x_{\text{upper}} - x_{\text{lower}})) ]

This ensures smooth percentile values even with small datasets.

Basic Examples

90th percentile

=PERCENTILE(A1:A100; 0.9)

25th percentile

=PERCENTILE(A1:A100; 0.25)

Median (50th percentile)

=PERCENTILE(A1:A100; 0.5)

Minimum and maximum via percentiles

=PERCENTILE(A1:A100; 0)
=PERCENTILE(A1:A100; 1)

Advanced Examples

Percentile across sheets

=PERCENTILE(Sheet1.A1:A500; 0.95)

Percentile with dynamic percentile value

=PERCENTILE(A1:A100; B1)

Where B1 contains a value like 0.8.

Conditional percentile (workaround)

Percentile of B where region = “North”:

=PERCENTILE(IF(A1:A100="North"; B1:B100); 0.9)

Confirm with Ctrl+Shift+Enter.

Percentile of filtered data

=PERCENTILE(IF(SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0)); A1:A100); 0.75)

Confirm with Ctrl+Shift+Enter.

Percentile for grading curves

=PERCENTILE(Scores; 0.85)

Percentile for outlier detection

Upper bound:

=PERCENTILE(A1:A100; 0.975)

Lower bound:

=PERCENTILE(A1:A100; 0.025)

Common Errors and Fixes

PERCENTILE returns Err:502 (Invalid argument)

Occurs when:

  • k < 0 or k > 1
  • Range contains no numeric values
  • k is text
  • Range is malformed

PERCENTILE returns unexpected values

Possible causes:

  • Dataset not sorted (sorting is not required, but helps interpretation)
  • Text numbers not converted to numeric
  • Hidden blanks affecting interpolation

PERCENTILE ignores values you expected it to include

PERCENTILE ignores:

  • Text numbers ("123")
  • Empty cells
  • Logical values
  • Errors

PERCENTILE includes values you expected it to ignore

PERCENTILE includes:

  • Dates
  • Times
  • Numeric results of formulas

Err:508 — Missing parenthesis

Usually caused by:

  • Missing )
  • Using commas instead of semicolons

Best Practices

  • Use percentiles to understand distribution shape
  • Use 0.25, 0.5, 0.75 for quartile analysis
  • Use array formulas for conditional percentiles
  • Convert imported text numbers to real numbers
  • Use named ranges for cleaner formulas
  • Use percentiles for outlier detection and grading curves
PERCENTILE is the backbone of quartiles, box plots, and distribution analysis — mastering it unlocks deeper statistical insight.