MEDIAN Function (LibreOffice Calc)
The MEDIAN function in LibreOffice Calc returns the middle value of a dataset. This guide explains syntax, examples, edge cases, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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.