Excel MIN Function Returns 0: 2 Possible Scenarios

We’re going to cover the cases where the Excel MIN function returns 0, as well as some workarounds to deal with this. Here’s an overview of the dataset we’ll use.

Excel MIN Function Returns 0: 2 Possible Scenarios

The MIN function returns 0 in two specific cases: if any of the cells within the argument of the MIN function contains 0, or the cell contains non numerical values.

Reason 1 – When Any of the Cells in the Range Is 0

We have 0 values in our dataset under the heading of Quantity Sold. If we apply the MIN function for the range E6:E13, the MIN function will return 0.

=MIN(E6:E13)

Read More: How to Find Minimum Value in Excel

Reason 2 – If the Cells Contain Non-Numerical Values

The MIN function can also return 0 if the range of cells that we’re passing as the argument of this function contain non numerical values. For example, the Product Name contains values that are non-numerical. So, if we apply the MIN function on this range of cells, we’ll get 0 as output.

=MIN(C6:C13)

How to Return a Non-Zero Lowest Value of a Range in Excel (2 Examples)

Example 1 – Use of MIN and IF Functions

Use the following formula in cell B16 and hit Enter.

=MIN(IF(E6:E13>0,E6:E13))

This formula first filters out the values greater than 0 in the range E6:E13. Then, it finds the minimum value from the filtered values. In this way, we get the lowest non zero values from a range of cells.

Example 2 – Use of SMALL and COUNTIF Functions

Use the following formula in the cell B16 and press Enter.

=SMALL(E6:E13,COUNTIF($E$6:$E$13,0)+1)

Formula Breakdown:

COUNTIF($E$6:$E$13,0)

This counts the number of times that the value 0 appears in the range E6:E13.

COUNTIF($E$6:$E$13,0)+1

We’ve used +1 at the end of the formula to add 1 to the count of the number of times 0 appears in the range. So this expression returns 3 as 0 is present two times in the range.

SMALL(E6:E13,COUNTIF($E$6:$E$13,0)+1)

This is now simplified as SMALL(E6:E13,3). This returns the 3rd smallest value including the repeating 0 values from the range E6:E13.

We get the lowest non zero value from the selected range of cells E6:E13.

How to Ignore Blanks with the Excel MIN Function

Method 1 – Returning 0 for Numeric Values

Use the following formula in the cell B16 and press Enter.

=IF(COUNTBLANK(E6:E13)>0,"0",MIN(E6:E13))

Formula Breakdown:

=IF(COUNTBLANK(E6:E13)>0,”0″,MIN(E6:E13))

The COUNTBLANK function counts the number of blank cells in the range E6:E13. If the count value is greater than 0, the IF function returns 0. Otherwise, the IF function returns the earliest date in the range D6:D13 using the MIN function.

Method 2 – Excel MIN Function Returning 0 for Date

Use the following formula in the cell B16 and press Enter.

=IF(COUNTBLANK(D6:D13)>0,"0",MIN(D6:D13))

Formula Breakdown:

=IF(COUNTBLANK(D6:D13)>0,”0″,MIN(D6:D13))

The COUNTBLANK function counts the number of blank cells in the range D6:D13. If the count value is greater than 0, the IF function returns 0. Otherwise, the IF function returns the earliest date in the range D6:D13 using the MIN function.

Download the Practice Workbook

MIN Function Returning 0.xlsx