MODE Function (OpenOffice Calc)
The MODE function in OpenOffice Calc returns the most frequently occurring number in a dataset. Learn syntax, examples, behavior with ties, common errors, and best practices.
Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked
Availability and version history
- Introduced: Documented by Apache OpenOffice 4.1.16; original introduction not established.
- Availability: Supported in Apache OpenOffice 4.1.16, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The MODE function in OpenOffice Calc returns the most frequently occurring number in a dataset. It is one of the three major measures of central tendency (along with AVERAGE and MEDIAN) and is especially useful for identifying common values, repeated measurements, or dominant categories in numeric data.
What the MODE Function Does
- Returns the most common numeric value
- Ignores text and empty cells
- Includes dates and times (because they are numeric)
- Works across sheets
- Useful for frequency analysis, statistics, and data profiling
MODE is ideal for identifying repeated values or dominant patterns.
Syntax
MODE(number1; number2; ...)
Arguments:
- number1, number2, … — Individual values, cell references, or ranges
How MODE Handles Ties
- If one value occurs most frequently → MODE returns that value
- If multiple values tie for highest frequency → MODE returns the first one encountered
- If no values repeat → MODE returns Err:502
Example:
Values: 5, 7, 7, 9, 9
Result: 7 (first mode)
Basic Examples
Mode of a range
=MODE(A1:A10)
Mode of multiple ranges
=MODE(A1:A10; C1:C10)
Mode of a list of values
=MODE(10; 25; 7; 25; 3)
Result: 25
Mode of dates
If A1:A5 contains dates:
=MODE(A1:A5)
Result: the most frequently occurring date.
Advanced Examples
Mode across sheets
=MODE(Sheet1.A1:A100)
Mode with conditions (workaround)
OpenOffice Calc does not have MODEIF, but you can use:
=MODE(IF(A1:A100="North"; B1:B100))
Confirm with Ctrl+Shift+Enter.
Mode within a date range
=MODE(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12;31)); B1:B100))
Confirm with Ctrl+Shift+Enter.
Mode excluding zeros
=MODE(IF(A1:A100<>0; A1:A100))
Confirm with Ctrl+Shift+Enter.
Mode of filtered data
MODE does not ignore filtered rows.
Workaround:
=MODE(IF(SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0)); A1:A100))
Confirm with Ctrl+Shift+Enter.
Mode in a 3D range
=MODE(Sheet1:Sheet5.A1:A10)
Common Errors and Fixes
MODE returns Err:502 (Invalid argument)
Occurs when:
- No value repeats
- All values are text
- All values are empty
- Imported numbers stored as text
Fix:
Convert text to numbers:
Data → Text to Columns → OK
MODE returns the wrong value
Possible causes:
- Multiple values tie for highest frequency
- Hidden duplicates
- Text numbers not matching numeric values
MODE includes values you expected it to ignore
MODE includes:
- Dates
- Times
- Numeric results of formulas
MODE excludes values you expected it to include
MODE ignores:
- Text numbers (
"123") - Empty cells
- Logical values (TRUE/FALSE)
- Errors
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
Best Practices
- Use MODE to identify repeated values
- Use array formulas for conditional modes
- Convert imported text numbers to real numbers
- Avoid mixing text and numbers in the same column
- Use named ranges for cleaner formulas
- Combine MODE with FREQUENCY for deeper analysis