Browse

MEDIAN Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 3.0

The MEDIAN function in LibreOffice Calc returns the middle value of a dataset. This guide explains syntax, examples, edge cases, errors, and best practices.

The **MEDIAN** function returns the middle value of a dataset. It is one of the core measures of central tendency in statistics, especially useful when your data contains outliers or skewed values.

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

Tested visual example: Middle reading in a dataset

What this example answers: Find the central value even when readings contain an extreme value. The formula was entered and recalculated in LibreOffice Calc 25.2.3.2, not simulated or inferred from a reference page.

Sample sheet you can reproduce

Create a sheet named Example, put these headings on row 4, and enter the sample values in rows 5–10. Or download the editable MEDIAN practice workbook with the formulas already entered.

Sheet row A: Reading ID B: Technician C: Reading D: Status
5 R-01 Ana 10 Valid
6 R-02 Ben 14 Valid
7 R-03 Cara 8 Valid
8 R-04 Dan 22 Valid
9 R-05 Eva 12 Valid
10 R-06 Fox 16 Valid

Example 1 — Formula and expected result

Select cell E5, enter the formula and press Enter:

=MEDIAN(C5:C10)

Verified result: 13.0.

With six readings, MEDIAN averages the two middle values (12 and 14) after sorting.

Actual LibreOffice Calc 25.2 window showing MEDIAN formula in the formula bar, example input rows and the calculated result in cell E5

Genuine screenshot from LibreOffice Calc 25.2.3.2: the formula bar, input table and computed output are visible. No interface imagery was generated.

Example 2 — Change the inputs

The same downloadable .ods file has this second formula in E12:

=MEDIAN(C5:C9)

Verified result: 12.0. Use only the first five observations. With an odd number of readings, the median is the single central value.

How to avoid misleading results

  • Watch for this mistake: MEDIAN does not describe how spread out the readings are; consider an IQR or standard deviation as well.
  • Scope and limitations: The function ignores empty cells in a range but retains zero values.
  • Argument separators: These examples use semicolons (;) in the LibreOffice Calc locale tested. Other locales may use different settings.
  • Verification scope: The two formulas in this section were executed and checked in LibreOffice Calc 25.2.3.2. Older examples elsewhere in this article have not all been independently retested.

Official function reference: LibreOffice Help — MEDIAN.

What the MEDIAN Function Does

  • Returns the middle value of a sorted dataset
  • Works with numbers, ranges, and mixed arguments
  • Ignores empty cells automatically
  • Handles odd and even dataset sizes
  • Works across sheets
  • Robust against outliers (unlike AVERAGE)

MEDIAN is ideal for analyzing skewed or unevenly distributed data.

Syntax

MEDIAN(number1; number2; ...)
LibreOffice Calc uses semicolons (;) to separate arguments.
Text values are ignored unless they represent numbers.

Basic Examples

Median of a range

=MEDIAN(A1:A10)

Median of multiple ranges

=MEDIAN(A1:A10; C1:C10)

Median of specific values

=MEDIAN(5; 12; 19)

Mixed values and references

=MEDIAN(A1:A10; 25; C5)

How MEDIAN Handles Odd vs. Even Counts

Odd number of values

=MEDIAN(1; 5; 9)

Result: 5

Even number of values

=MEDIAN(1; 5; 9; 13)

Result: (5 + 9) / 2 = 7

Advanced Examples

Median across sheets

=MEDIAN(Sheet1.A1:A10; Sheet2.A1:A10)

Median ignoring errors (using AGGREGATE)

=AGGREGATE(12; 2; A1:A10)

Median of visible cells only (filtered data)

=AGGREGATE(12; 1; A1:A10)

Median with conditions (indirect)

LibreOffice has no MEDIANIF, but you can simulate it:

=MEDIAN(IF(A1:A10="North"; B1:B10))

(Confirm with Ctrl+Shift+Enter if using older Calc versions.)

Median of top 10 values

=MEDIAN(LARGE(A1:A100; ROW(1:10)))

Median of bottom 10 values

=MEDIAN(SMALL(A1:A100; ROW(1:10)))

Median excluding zeros

=MEDIAN(IF(A1:A100<>0; A1:A100))

Common Errors and Fixes

MEDIAN returns 0 unexpectedly

Possible causes:

  • Zeros included in the dataset
  • Text values interpreted as zero
  • Hidden rows included

Fix:
Filter out zeros or use conditional MEDIAN techniques.

MEDIAN returns Err:502 (Invalid argument)

Occurs when:

  • A non-numeric text value is passed
  • A range reference is malformed

MEDIAN returns wrong result with filtered data

MEDIAN does not ignore hidden rows.
Use:

=AGGREGATE(12; 1; A1:A10)

Best Practices

  • Use MEDIAN instead of AVERAGE when data contains outliers
  • Use AGGREGATE for visibility‑aware medians
  • Use array formulas for conditional medians
  • Clean imported data before analysis
  • Use named ranges for cleaner formulas
MEDIAN is one of the most reliable measures of central tendency—especially when your dataset is skewed or contains extreme values.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

=MEDIAN(2;9;5)

Expected result: 5.

Find the middle observation in a sorted set.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.