How to Find the Second-Smallest Value in Nonadjacent Excel Cells in WPS Spreadsheets
Question details
The user needs to return the second-smallest numeric value from selected nonadjacent cells by using the SMALL function with a parenthesized list of cell references.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has numeric values in nonadjacent cells such as A1, A5, A8, A12, and A50 and wants to calculate the second-smallest value without rearranging the worksheet.
- Observed behavior
- The goal state is a formula that returns the second-smallest numeric value from the selected nonadjacent cell references.
Confirm that the referenced cells contain numeric values and are located on the same worksheet. In WPS Spreadsheets, nonadjacent references used as a single SMALL argument must be wrapped in parentheses, for example (A1,A5,A8,A12,A50).
Use SMALL with a Parenthesized List of Nonadjacent Cell References
Use this method when you have numeric values scattered in nonadjacent cells and need to return the second-smallest value without moving the data.
The SMALL function normally expects one array or range. Wrapping nonadjacent cell references in parentheses lets WPS Spreadsheets treat them as one union reference, so SMALL can evaluate all listed cells in a single argument.
Click the cell where you want the second-smallest value to appear.
Type =SMALL((A1,A5,A8,A12,A50),2) into the formula bar. Replace the cell references with the nonadjacent locations in your worksheet.
Press Enter to calculate the formula. WPS Spreadsheets returns the second-smallest numeric value from the referenced cells.
Find the Second-Smallest Value in Nonadjacent Cells with WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible formulas such as SMALL, so you can enter =SMALL((A1,A5,A8,A12,A50),2) directly and return the second-smallest numeric value from nonadjacent cells without reorganizing your data.
- 1. Download and install WPS Office: Get WPS Office Free from the official WPS website and complete the installation.
- 2. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the Excel workbook that contains the nonadjacent cells you want to evaluate.
- 3. Enter the SMALL formula: Click the result cell, type =SMALL((A1,A5,A8,A12,A50),2), and replace the references with your own nonadjacent cells.
- 4. Press Enter to calculate: Press Enter to display the second-smallest numeric value from the selected cells.
Frequently Asked Questions
Why do I need parentheses around the nonadjacent cell references in SMALL?
The parentheses group the comma-separated cells into one union reference. Without grouping, WPS Spreadsheets may treat each cell as a separate argument and SMALL will not calculate correctly. Use =SMALL((A1,A5,A8,A12,A50),2).
Can I change 2 to another number to find the minimum or third-smallest value?
Yes. The second argument is k; use 1 for the minimum, 2 for the second-smallest, 3 for the third-smallest, and so on. If k exceeds the count of numeric cells, SMALL returns a #NUM! error.
What happens if one of the referenced nonadjacent cells contains text or is empty?
SMALL ignores text and empty cells in the referenced union and evaluates only numeric values. If no numeric values exist, it returns a #NUM! error.
Does this SMALL formula work in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports Excel-compatible formulas and .xlsx files, so the same =SMALL((A1,A5,A8,A12,A50),2) formula can be used when moving between the two applications.
Can I use named ranges or table references in the SMALL formula?
You can use named ranges, but nonadjacent cells still need to be grouped in parentheses when passed as one array argument, for example =SMALL((Range1,Range2),2).
