Menu

PERCENTILE Function (OpenOffice Calc)

Statistical Intermediate OpenOffice Calc Introduced in OpenOffice.org 3.0
percentile distribution statistics interpolation numeric-data

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.

Compatibility

â–¾

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.

Related Functions

Copyright 2026. All rights reserved.