SMALL Function (LibreOffice Calc)
The SMALL function in LibreOffice Calc returns the k-th smallest 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 SMALL Function Does ▾
- Returns the k‑th smallest 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
SMALL is the inverse of LARGE.
Syntax ▾
SMALL(range; k)
k must be a positive integer between 1 and the number of numeric values in the range.
Basic Examples ▾
Smallest value (k = 1)
=SMALL(A1:A10; 1)
Second smallest value
=SMALL(A1:A10; 2)
Fifth smallest value
=SMALL(A1:A10; 5)
Using a cell reference for k
=SMALL(A1:A10; B1)
Smallest value across multiple ranges
=SMALL((A1:A10; C1:C10); 1)
Advanced Examples ▾
Extract the 1st through 10th smallest values (array)
=SMALL(A1:A100; ROW(1:10))
Conditional SMALL (indirect)
LibreOffice has no SMALLIF, but you can simulate it:
=SMALL(IF(B1:B100="North"; A1:A100); 1)
(Confirm with Ctrl+Shift+Enter in older Calc versions.)
SMALL ignoring errors (using AGGREGATE)
=AGGREGATE(15; 2; A1:A10; 3)
SMALL of visible cells only (filtered data)
=AGGREGATE(15; 1; A1:A10; 3)
SMALL excluding zeros
=SMALL(IF(A1:A100<>0; A1:A100); 1)
SMALL across sheets
=SMALL((Sheet1.A1:A10; Sheet2.A1:A10); 3)
SMALL for percentile extraction
=SMALL(A1:A100; ROUNDUP(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
SMALL returns wrong result with filtered data
SMALL does not ignore hidden rows.
Use:
=AGGREGATE(15; 1; A1:A10; k)
SMALL includes zeros unexpectedly
Filter them out:
=SMALL(IF(A1:A100<>0; A1:A100); k)
Best Practices ▾
- Use SMALL for ranking and ordered extraction
- Use AGGREGATE for visibility‑aware or error‑tolerant SMALL
- Use array formulas for conditional or multi‑value extraction
- Clean imported data before analysis
- Use named ranges for cleaner formulas
SMALL is perfect for extracting the lowest N values, building rankings, and performing percentile‑based analysis.