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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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