PrecisionCalc
xl
Precision
Get Your Numbers Right
xlpSMALL
Returns the k-th smallest number in a cell range, with up to 32,767 significant digits of precision. Same as Excel's built-in SMALL function, but with high precision.
Syntax
xlpSMALL(numlist,k)
| numlist | Required. The array or range of cells with the numbers to sort. |
| k | Required. The position (from the smallest) in the cell range to return. |
Remarks
numlist can accept cells with both numbers and text.
numlist can accept cells with text formatted with the local currency symbol and thousands separators, and negatives can be formatted with either a leading hyphen or parentheses.
The return value will be formatted however the chosen input is formatted. To specify number formatting to apply to the return value, xlpSMALL can be formatted by wrapping it in other xlPrecision functions such as xlpADD. See example below.
Examples
| Formula | Description | Result | ||||||||||||||||||||||||||||||||||||
=xlpSMALL(A1:C5,3)
|
The 3rd-smallest number in A1:C5. | 30 | ||||||||||||||||||||||||||||||||||||
=xlpADD(xlpSMALL(A1:C5,3),0,,TRUE)
|
The 3rd-smallest number in A1:C5, formatted with thousands separators by wrapping xlpSMALL in xlpADD (adding the result of xlpSMALL to 0 to get the same result) and specifying thousands separators in xlpADD. | 30,000 | ||||||||||||||||||||||||||||||||||||
=xlpSMALL(A1:A17,5)
|
The 5th-smallest number in A1:A17. | 1,234,567,890,123,456,789.012345 |
See Also