LARGE Function (OpenOffice Calc)
The LARGE function in OpenOffice Calc returns the k-th largest value in a dataset. Learn syntax, examples, ranking behavior, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the LARGE Function Does ▾
- Returns the k‑th largest numeric value
- Ignores text and empty cells
- Includes dates and times (because they are numeric)
- Works across sheets
- Useful for ranking, leaderboards, and top‑N analysis
LARGE is ideal for extracting top performers or highest values in sorted order.
Syntax ▾
LARGE(range; k)
Arguments:
- range — The dataset to evaluate
- k — The rank (1 = largest, 2 = second largest, etc.)
k must be a positive integer and cannot exceed the number of numeric values in the range.
Basic Examples ▾
Find the largest value (same as MAX)
=LARGE(A1:A10; 1)
Find the second largest value
=LARGE(A1:A10; 2)
Find the fifth largest value
=LARGE(A1:A10; 5)
Find the k‑th largest using a cell reference
=LARGE(A1:A10; B1)
If B1 contains 3, the formula returns the 3rd largest value.
Advanced Examples ▾
LARGE across sheets
=LARGE(Sheet1.A1:A100; 3)
LARGE with conditions (workaround)
OpenOffice Calc does not have LARGEIF, but you can use:
=LARGE(IF(A1:A100="North"; B1:B100); 1)
Confirm with Ctrl+Shift+Enter.
Top‑N list (dynamic)
To list the top 5 values:
=LARGE($A$1:$A$100; ROW(A1))
Copy down 5 rows.
LARGE within a date range
=LARGE(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12;31)); B1:B100); 1)
Confirm with Ctrl+Shift+Enter.
LARGE excluding zeros
=LARGE(IF(A1:A100<>0; A1:A100); 1)
Confirm with Ctrl+Shift+Enter.
LARGE in a 3D range
=LARGE(Sheet1:Sheet5.A1:A10; 2)
Common Errors and Fixes ▾
LARGE returns Err:502 (Invalid argument)
Occurs when:
kis greater than the number of numeric valueskis zero or negative- All values are text or empty
- Imported numbers stored as text
Fix: Convert text to numbers:
Data → Text to Columns → OK
LARGE returns the wrong value
Possible causes:
- Hidden text numbers
- Hidden spaces
- Mixed data types
LARGE includes values you expected it to ignore
LARGE includes:
- Dates
- Times
- Numeric results of formulas
LARGE excludes values you expected it to include
LARGE 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 LARGE for ranking and top‑N analysis
- Use SMALL for bottom‑N analysis
- Use array formulas for conditional ranking
- Convert imported text numbers to real numbers
- Avoid mixing text and numbers in the same column
- Use named ranges for cleaner formulas
To build a Top 10 leaderboard, combine LARGE with INDEX/MATCH to return the corresponding names or categories.