Contact
12 programs
Explore the site

SMALL Function (LibreOffice Calc)

Math Beginner LibreOffice Calc Introduction not established

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.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
  • Availability: Supported in LibreOffice 26.8.1.1, 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 returns the *k‑th smallest* value in a dataset. It is essential for ranking, statistical analysis, percentile calculations, and extracting ordered values from a range.

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:

  • k is less than 1
  • k is 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.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

Use this small practice sheet for the ranges referenced in the formula:

Row A (Quantity) B (Amount) C (Category)
1 1 10 apple
2 2 20 pear
3 3 30 apple
4 4 40 pear
5 5 50 apple 
=SMALL(B1:B5;2)

Expected result: 20.

Find the second-smallest amount in the sample list.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.