How to Find the Nth Smallest or Largest Positive Value in Excel
Question details
The user wants to extract the nth smallest or largest positive numbers from a given array while ignoring zero values and avoiding division-by-zero errors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data analysis where numerical ranking is required, but zero values or specific errors need to be excluded from the dataset before applying SMALL or LARGE functions.
- Observed behavior
- The user needs a clean, dynamic array formula to rank values accurately without standard errors or zeroes interfering with the results.
Ensure your spreadsheet software supports dynamic array functions such as FILTER, TOCOL, and LET, which are required to run these advanced nested formulas.
Use LET and FILTER with SMALL or LARGE Functions
Combine these dynamic array functions to first filter out zeroes and then extract the desired ranked positive values.
The LET function allows you to define the array range once, making the formula cleaner and easier to read. The FILTER function processes this array to remove any values less than or equal to zero, ensuring only positive numbers are passed to the SMALL or LARGE function.
Click the cell where you want the ranked results to display (for example, F2).
Type the formula =LET(rng,TOCOL(A2:D4),SMALL(FILTER(rng,rng>0),{1;2;3})) to extract the 1st, 2nd, and 3rd smallest positive numbers from the range A2:D4.
Press Enter. The results will automatically spill into adjacent cells based on your array constant.
To find the largest positive values instead, simply replace SMALL with LARGE in the formula: =LET(rng,TOCOL(A2:D4),LARGE(FILTER(rng,rng>0),{1;2;3})).
Calculate Complex Array Formulas Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like FILTER, LET, SMALL, and LARGE, allowing you to easily find ranked positive values without worrying about zero errors.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Input the array formula: Click on your target cell and type the LET and FILTER combination formula.
- 3. Calculate the result: Press Enter, and WPS Spreadsheet will instantly calculate and spill the dynamic array results.
Frequently Asked Questions
Why am I getting a #CALC! error with the FILTER function?
The #CALC! error occurs when the FILTER function finds no values that meet the criteria (e.g., if there are no positive numbers in the selected range). You can add a default return value as the third argument in the FILTER function to handle this gracefully, such as =FILTER(rng, rng>0, "No positive values").
How can I find only the single smallest positive value instead of an array?
Simply replace the array constant {1;2;3} with 1 in the formula. For example: =LET(rng, TOCOL(A2:D4), SMALL(FILTER(rng, rng>0), 1)).
How does the TOCOL function help in this formula?
TOCOL transforms a multi-row and multi-column range (like A2:D4) into a single column array. This makes it significantly easier for the FILTER and SMALL/LARGE functions to process the data linearly without missing values across different columns.
Can I use this formula in older versions of Excel?
No, functions like FILTER, TOCOL, and LET are dynamic array functions introduced in newer versions of Excel and WPS Office. For older versions, you would need to use complex array formulas entered with Ctrl+Shift+Enter utilizing nested IF statements.
