SMALL Function (OpenOffice Calc)
The SMALL function in OpenOffice Calc returns the k-th smallest value in a dataset. Learn syntax, examples, ranking behavior, 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 SMALL function in OpenOffice Calc returns the k‑th smallest value in a dataset. It is essential for bottom‑N analysis, identifying minimum performers, statistical ranking, and any scenario where you need to extract the lowest values in order.
What the SMALL Function Does
- Returns the k‑th smallest numeric value
- Ignores text and empty cells
- Includes dates and times (because they are numeric)
- Works across sheets
- Useful for ranking, bottom‑N analysis, and statistical profiling
SMALL is ideal for identifying low performers or minimum values in sorted order.
Syntax
SMALL(range; k)
Arguments:
- range — The dataset to evaluate
- k — The rank (1 = smallest, 2 = second smallest, etc.)
k must be a positive integer and cannot exceed the number of numeric values in the range.
Basic Examples
Find the smallest value (same as MIN)
=SMALL(A1:A10; 1)
Find the second smallest value
=SMALL(A1:A10; 2)
Find the fifth smallest value
=SMALL(A1:A10; 5)
Find the k‑th smallest using a cell reference
=SMALL(A1:A10; B1)
If B1 contains 3, the formula returns the 3rd smallest value.
Advanced Examples
SMALL across sheets
=SMALL(Sheet1.A1:A100; 3)
SMALL with conditions (workaround)
OpenOffice Calc does not have SMALLIF, but you can use:
=SMALL(IF(A1:A100="North"; B1:B100); 1)
Confirm with Ctrl+Shift+Enter.
Bottom‑N list (dynamic)
To list the bottom 5 values:
=SMALL($A$1:$A$100; ROW(A1))
Copy down 5 rows.
SMALL within a date range
=SMALL(IF((A1:A100>=DATE(2025;1;1))*(A1:A100<=DATE(2025;12;31)); B1:B100); 1)
Confirm with Ctrl+Shift+Enter.
SMALL excluding zeros
=SMALL(IF(A1:A100<>0; A1:A100); 1)
Confirm with Ctrl+Shift+Enter.
SMALL in a 3D range
=SMALL(Sheet1:Sheet5.A1:A10; 2)
Common Errors and Fixes
SMALL 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
SMALL returns the wrong value
Possible causes:
- Hidden text numbers
- Hidden spaces
- Mixed data types
SMALL includes values you expected it to ignore
SMALL includes:
- Dates
- Times
- Numeric results of formulas
SMALL excludes values you expected it to include
SMALL 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 SMALL for bottom‑N analysis
- Use LARGE for top‑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