LARGE Function (LibreOffice Calc)
The LARGE function in LibreOffice Calc returns the k-th largest value in a dataset. This guide explains syntax, examples, edge cases, 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 value in a dataset
- Works with numbers, ranges, and mixed arguments
- Ignores empty cells automatically
- Useful for ranking, percentiles, and filtering
- Works across sheets
- Supports array‑based analysis
LARGE is the inverse of SMALL.
Syntax ▾
LARGE(range; k)
k must be a positive integer between 1 and the number of numeric values in the range.
Basic Examples ▾
Largest value (k = 1)
=LARGE(A1:A10; 1)
Second largest value
=LARGE(A1:A10; 2)
Fifth largest value
=LARGE(A1:A10; 5)
Using a cell reference for k
=LARGE(A1:A10; B1)
Largest value across multiple ranges
=LARGE((A1:A10; C1:C10); 1)
Advanced Examples ▾
Extract the 1st through 10th largest values (array)
=LARGE(A1:A100; ROW(1:10))
Conditional LARGE (indirect)
LibreOffice has no LARGEIF, but you can simulate it:
=LARGE(IF(B1:B100="North"; A1:A100); 1)
(Confirm with Ctrl+Shift+Enter in older Calc versions.)
LARGE ignoring errors (using AGGREGATE)
=AGGREGATE(14; 2; A1:A10; 3)
LARGE of visible cells only (filtered data)
=AGGREGATE(14; 1; A1:A10; 3)
LARGE excluding zeros
=LARGE(IF(A1:A100<>0; A1:A100); 1)
LARGE across sheets
=LARGE((Sheet1.A1:A10; Sheet2.A1:A10); 3)
LARGE for percentile extraction
=LARGE(A1:A100; ROUNDUP((1 - 0.9) * COUNT(A1:A100); 0))
(Used for 90th percentile.)
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
kis less than 1kis greater than the number of numeric values- The range contains no numeric values
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range is malformed
LARGE returns wrong result with filtered data
LARGE does not ignore hidden rows.
Use:
=AGGREGATE(14; 1; A1:A10; k)
LARGE includes zeros unexpectedly
Filter them out:
=LARGE(IF(A1:A100<>0; A1:A100); k)
Best Practices ▾
- Use LARGE for ranking and ordered extraction
- Use AGGREGATE for visibility‑aware or error‑tolerant LARGE
- Use array formulas for conditional or multi‑value extraction
- Clean imported data before analysis
- Use named ranges for cleaner formulas
LARGE is perfect for extracting the highest N values, building rankings, and performing percentile‑based analysis.