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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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
To build a Bottom 10 list, combine SMALL with INDEX/MATCH to return the corresponding names or categories.