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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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
If no number repeats, MODE returns Err:502 (Invalid argument).
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
If your dataset has no repeating values, consider using MEDIAN or AVERAGE instead — they always return a result.