Find the Smallest Nonzero Value in Excel FILTER Results
Question details
Find the minimum value greater than zero in a data range while excluding zero values from the calculation.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs a formula that returns the smallest positive number from a named range without replacing zeros with a helper value.
- Observed behavior
- MIN would include zero as the minimum unless zero values are filtered out before the calculation.
Confirm that your data range contains real numeric values rather than numbers stored as text. If you use a named range, verify that the name, such as numbers, refers to the intended cells.
Use MIN with FILTER to return the smallest value greater than zero
Use this formula when you have Excel dynamic array functions and want to exclude zeros directly without a helper column or replacement value.
FILTER creates an array containing only values greater than zero, and MIN then returns the lowest value in that filtered array.
Click the empty cell where you want the smallest nonzero value to appear.
Type =MIN(FILTER(numbers,numbers>0)) and press Enter. Replace numbers with your named range or cell range, such as A2:A100.
For a regular range, enter =MIN(FILTER(A2:A100,A2:A100>0)) and press Enter.
Review the returned value; it should be the smallest number above zero in the selected range.
Convert text-formatted numbers before calculating the minimum
Use this method when values that look numeric are stored as text and are not being included correctly by the formula.
Handle ranges that contain no positive values
Use an error-handling version of the formula if the range may contain only zeros, negative values, blanks, or text.
Calculate the Smallest Nonzero Value with FILTER in WPS Spreadsheets
WPS Spreadsheets lets you filter out zero values and calculate the minimum positive value with a single formula. Enter the formula in a result cell, then use the same range or named-range logic for clean, dynamic calculations.
- 1. Open the worksheet in WPS Spreadsheets: Open your XLSX file in WPS Office and select the worksheet containing the numeric data.
- 2. Choose the output cell: Click an empty cell where the smallest nonzero result should be displayed.
- 3. Enter the formula: Type =MIN(FILTER(numbers,numbers>0)), or replace numbers with a range such as A2:A100.
- 4. Confirm the calculation: Press Enter to return the smallest value greater than zero from the filtered results.
Frequently Asked Questions
What formula returns the smallest value above zero in Excel?
Use =MIN(FILTER(numbers,numbers>0)). Replace numbers with your named range or use a cell range, such as =MIN(FILTER(A2:A100,A2:A100>0)).
Why does MIN return zero instead of the lowest positive number?
MIN evaluates every numeric value in its reference, including zero. Filter the source range with the condition >0 before passing the results to MIN.
Can I use this formula without a named range?
Yes. Replace the named range with the same cell range in both arguments, for example: =MIN(FILTER(A2:A100,A2:A100>0)).
What happens if there are no values greater than zero?
FILTER returns an error when no entries meet the condition. Use =IFERROR(MIN(FILTER(A2:A100,A2:A100>0)),"No positive values") to show a custom message instead.
Why are some numeric-looking cells ignored by FILTER?
The cells may contain text rather than real numbers. Select the cells and use Convert to Number, or convert them with VALUE before calculating the minimum.
