To find the second smallest value from a set of formulas in Excel, use the
SMALL function with an array reference. Instead of referencing static cells, wrap your formula range in parentheses or use it directly if it spills into an array. For example, if your calculations are in
B2:B10, the syntax is
=SMALL(B2:B10, 2). This approach dynamically evaluates the results of those formulas to identify the target rank without requiring manual data entry or helper columns.
Understanding the SMALL Function Logic
The core tool for this task is the
SMALL worksheet function. It returns the k-th smallest value in a data set. The syntax requires two arguments: the array or range containing the numbers, and the integer
k that specifies which position you want to retrieve. When dealing with formulas, the "data set" is not a fixed block of text but rather the computed outputs of those formulas.
If you have a column where each cell contains a complex calculation—such as
=A2*1.1 or
=VLOOKUP(...)—Excel treats the resulting values as the dataset. You do not need to copy-paste these as values. The
SMALL function can evaluate the live results. However, you must ensure that the range you pass to the function covers all the formula outputs you wish to consider.
Applying SMALL to Formula Ranges
Consider a scenario where Column B contains formulas that calculate monthly sales based on varying rates. To find the second lowest sales figure, you reference the entire range of these formula cells.
=SMALL(B2:B20, 2)
This single formula looks at the calculated results in
B2 through
B20, sorts them internally, and picks the second one. If there are duplicate values,
SMALL treats them as distinct items for ranking purposes. For instance, if the sorted values are 10, 20, 20, 30, the first smallest is 10, and the second smallest is 20. This behavior is often desirable in financial modeling where ties are common.
For more dynamic ranges, especially when new rows are added frequently, consider using a Table structure. Convert your data range into an Excel Table (Ctrl+T). Then, reference the structured column name in the
SMALL function. This ensures that as you add new formulas to the bottom of the table, the range automatically expands, and your second-smallest calculation remains accurate without manual adjustment.
Handling Errors and Non-Numeric Results
A common pitfall when working with formula sets is encountering error values like
#N/A,
#VALUE!, or
#DIV/0!. The standard
SMALL function will return an error if any cell in the referenced range contains an error. To solve this, you must filter out errors before passing the data to
SMALL.
You can achieve this by nesting the
IFERROR function inside the array logic. One robust method involves using an array formula that replaces errors with a large number (so they are ignored in a "smallest" search) or excludes them entirely.
=SMALL(IF(ISNUMBER(B2:B20), B2:B20), 2)
In older versions of Excel, you must confirm this array formula by pressing Ctrl+Shift+Enter. In modern Excel with dynamic arrays, simply pressing Enter is sufficient. This formula checks if each cell in the range is a number. If it is, it includes it in the evaluation; if it is an error or text, it skips it. This ensures that only valid numeric results from your formulas compete for the "second smallest" title.
Integrating with Other Data Operations
Sometimes, finding the second smallest value is part of a larger data analysis workflow. For example, you might need to sum cells based on specific criteria while simultaneously identifying key statistical outliers. While
SMALL handles the ranking, you may combine it with
SUMPRODUCT or
AVERAGEIFS for deeper insights.
If you are managing multiple spreadsheets and need to pull this statistic from various sources, understanding how to retrieve data from multiple cells on different spreadsheets can streamline your process. You can link the
SMALL result to a dashboard sheet that aggregates metrics from several workbooks. Additionally, if your formulas depend on user selections, such as dropdown menus, you can make the
k argument dynamic. Instead of hardcoding
2, reference a cell that contains the desired rank. This allows users to switch between finding the minimum, second minimum, or third minimum instantly.
For further reading on managing complex spreadsheet interactions, see
How to Retrieve Data from Multiple Cells on Different Spreadsheets and Add to One Single Cell on Another Spreadsheet. This resource explains how to connect disparate data sources, which is useful if your formula results are scattered across different sheets.
Another related technique involves automating routine tasks. If you find yourself repeatedly calculating statistics from changing formula sets, learning about
Automating Formulas in Microsoft Excel can help you build templates that update these rankings automatically upon file open or data change.
Frequently Asked Questions
What if my formula range contains blank cells?
Blank cells are generally ignored by the
SMALL function, treating them as zero or non-existent depending on context. However, to be safe, ensure your range does not include unintended empty rows at the end if you are using dynamic arrays. Using
IFERROR or checking for blanks with
ISBLANK inside an
IF statement provides cleaner control over the dataset.
Can I use SMALL with horizontal ranges?
Yes, the
SMALL function works identically with horizontal ranges. Simply replace the vertical range reference (e.g.,
B2:B20) with a horizontal one (e.g.,
B2:X2). The logic remains the same: it evaluates the values in that row and returns the k-th smallest item.
Why is my SMALL function returning #NUM! error?
This error occurs when the
k argument is larger than the number of numeric values in the range. For example, if you ask for the 5th smallest value but only have 3 valid numbers, Excel cannot fulfill the request. Ensure your
k value is within the count of available data points.
Does SMALL ignore negative numbers?
No,
SMALL considers negative numbers as smaller than positive ones. If your dataset contains -10, 5, and 20, the smallest is -10, and the second smallest is 5. Be careful if you intended to find the second smallest *absolute* value, in which case you should wrap your range in
ABS().
How do I find the second largest instead?
Use the
LARGE function instead of
SMALL. The syntax is identical:
=LARGE(range, 2). This function returns the k-th largest value, effectively mirroring the behavior of
SMALL but in descending order.