MEDIAN Function (OpenOffice Calc)
The MEDIAN function in OpenOffice Calc returns the middle value in a dataset. Learn syntax, examples, behavior with even/odd counts, common errors, and best practices.
The MEDIAN function in OpenOffice Calc returns the middle value in a dataset when the numbers are sorted. It is one of the most important measures of central tendency, especially for datasets with outliers, skewed distributions, or irregular spacing.
This guide provides syntax, examples, even/odd behavior, advanced techniques, common errors, and best practices, making it one of the most complete MEDIAN references available for OpenOffice Calc.
What the MEDIAN Function Does
- Returns the middle value of a dataset
- Ignores text and empty cells
- Works with odd or even numbers of values
- Includes dates and times (because they are numeric)
- Works across sheets
- Useful for statistical analysis, reporting, and data profiling
MEDIAN is ideal when you want a central value that is not distorted by outliers.
Syntax
MEDIAN(number1; number2; ...)
Arguments:
- number1, number2, … — Individual values, cell references, or ranges
How MEDIAN Works
Odd number of values
The median is the middle value.
Example:
Values: 5, 7, 9
Median: 7
Even number of values
The median is the average of the two middle values.
Example:
Values: 5, 7, 9, 11
Median: (7 + 9) / 2 = 8
Basic Examples
Median of a range
=MEDIAN(A1:A10)
Median of multiple ranges
=MEDIAN(A1:A10; C1:C10)
Median of a list of values
=MEDIAN(10; 25; 7; 99; 3)
Result: 10
Median of dates
If A1:A5 contains dates:
=MEDIAN(A1:A5)
Result: the middle date.
Advanced Examples
Median across sheets
=MEDIAN(Sheet1.A1:A100)
Median with conditions (workaround)
OpenOffice Calc does not have MEDIANIF, but you can use:
=MEDIAN(IF(A1:A100="North"; B1:B100))
Confirm with Ctrl+Shift+Enter.
Median within a date range
=MEDIAN(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12;31)); B1:B100))
Confirm with Ctrl+Shift+Enter.
Median excluding zeros
=MEDIAN(IF(A1:A100<>0; A1:A100))
Confirm with Ctrl+Shift+Enter.
Median of filtered data
MEDIAN does not ignore filtered rows.
Workaround:
=MEDIAN(IF(SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0)); A1:A100))
Confirm with Ctrl+Shift+Enter.
Median in a 3D range
=MEDIAN(Sheet1:Sheet5.A1:A10)
Common Errors and Fixes
MEDIAN returns 0 unexpectedly
Possible causes:
- All values are text
- All values are empty
- Imported numbers stored as text
- Hidden apostrophes (
'123)
Fix: Convert text to numbers:
Data → Text to Columns → OK
MEDIAN includes values you expected it to ignore
MEDIAN includes:
- Dates
- Times
- Numeric results of formulas
MEDIAN excludes values you expected it to include
MEDIAN ignores:
- Text numbers (
"123") - Empty cells
- Logical values (TRUE/FALSE)
- Errors
Err:502 — Invalid argument
Occurs when:
- A malformed range is used
- A text argument is not quoted properly
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
Best Practices
- Use MEDIAN instead of AVERAGE when data contains outliers
- Use array formulas for conditional medians
- Convert imported text numbers to real numbers
- Avoid mixing text and numbers in the same column
- Use named ranges for cleaner formulas
- Use MEDIAN for robust statistical summaries