You may use Excel’s MIN function to get the minimum value from a range of cells. In some cases your Excel MIN function returns 0. In this article, I’m going to demonstrate to you some practical use cases regarding this. I’m going to cover the cases where Excel MIN function returns 0 as well as some workarounds to deal with this. You may check the following image to get an overview of how it works.
Excel MIN Function Returns 0: 2 Possible Scenarios
The MIN function returns the smallest number in a set of values. It takes multiple numbers as arguments or a range of cells as the argument. And then, returns the smallest number from these values.
The MIN function returns 0 in two specific cases. This includes the cases if any of the cells within the argument of the MIN function contains 0 or the cell contains non numerical values. I’m going to show examples of both cases here so that you can understand it properly. Our dataset looks like the following image.
1. When Any of the Cells in the Range Is 0
If the range we’re working on contains the value 0, then by default MIN function returns 0. For example, we’ve 0 values in our dataset under the heading of Quantity Sold. So, if we apply the MIN function for the range E6:E13, the MIN function will return 0.
- To get the minimum Quantity Sold value, type the following formula in cell B16 and press ENTER. The output is what the MIN function returns. I’ve shown the formula in cell B17 using the FORMULATEXT function to ease your understanding.
=MIN(E6:E13)
From the image, we can see the cells E10 and E12 contain 0 value. So the output of the MIN function in the range E6:E13 is 0.
Read More: How to Find Minimum Value in Excel
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, that is the cells C6:C13, we’ll get 0 as output.
- Type the following formula in the cell B16 and press ENTER. This will return the minimum numerical value in the range of cells. I’ve shown the formula in the cell B17.
=MIN(C6:C13)
As we can see from the image that the argument of the MIN function contains non numeric values, so the MIN function returns 0.
Read More: How to Find Minimum Value That Is Greater Than 0 in Excel
How to Return Non Zero Lowest Value of a Range in Excel (2 Examples)
By default, the MIN function returns 0 if the lowest value in the range of cells that we’re using as the argument of the MIN function is 0. In some cases, we may actually need to get the non zero lowest value. We can find out that value in several ways. In this section, I’m going to demonstrate them.
1. Use of MIN and IF Functions
We can use the MIN function and the IF function together to get the non zero lowest value from a selection of cells.
- Type the following formula in cell B16 and hit ENTER. This will get us the lowest non zero value of Quantity Sold. I’ve shown the formula in the cell B17 so that you can understand it properly.
=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.
Read More: How to Find Lowest Value with Criteria in Excel
2. Use of SMALL and COUNTIF Functions
There is an alternative to using the MIN and IF functions together. In this case, we can use the SMALL function and the COUNTIF function together to get the lowest non zero value from the selected range of cells. This is a way to ignore the 0 value.
- Type the following formula in the cell B16 and press ENTER. This will return the lowest non zero value from the selected range of cells.
=SMALL(E6:E13,COUNTIF($E$6:$E$13,0)+1)
Formula Breakdown:
We’ll look at the formula from the inside first.
- 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.
Hence, we get the lowest non zero value from the selected range of cells E6:E13.
Read More: How to Find Lowest 3 Values in Excel
How to Ignore Blanks with Excel MIN Function
1. Returning 0 for Numeric Value
We use the MIN function to get the minimum value from a selected range of cells. Now, any of the selected cells is blank, the MIN function by default ignores that cell while finding out the minimum value from the selected range of cells. As a result, the MIN function returns non zero lowest value from the selected range of cells provided that no 0 value is present in the selected range of cells.
Now, if we want to get 0 as return value of the MIN function if a blank cell exists, then we can do this too.
- Type the following formula in the cell B16 and press ENTER. This returns the value 0 as there are blank cells present in the selected range of cells.
=IF(COUNTBLANK(E6:E13)>0,"0",MIN(E6:E13))
Formula Breakdown:
=IF(COUNTBLANK(E6:E13)>0,”0″,MIN(E6:E13))
In this formula, 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.
Read More: How to Use MIN Function to Exclude Zero in Excel
2. Excel MIN Function Returning 0 for Date
We can use the MIN function to get the earliest date from the selected range of cells. By default, if any of the cells that we’re using as an argument of the MIN function is blank, the MIN function ignores those blank cells and finds the earliest date considering only cells with valid values. Now, if we want the MIN function to return 0 if any cell within the argument of the MIN function is blank, we can do this using IF, COUNTBLANK function and MIN functions together.
- Type the following formula in the cell B16 and press ENTER. The output will be 0 as there are blank cells in the argument of the MIN
=IF(COUNTBLANK(D6:D13)>0,"0",MIN(D6:D13))
Formula Breakdown:
=IF(COUNTBLANK(D6:D13)>0,”0″,MIN(D6:D13))
In this formula, 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.
Things to Remember
While working on the MIN function, keep in mind that, the MIN function works on only numeric values. So, before applying the MIN function, double-check that the argument of the MIN function is numeric. To ensure this, you may either format the cell values as Automatic or as Number.
Download Practice Workbook
You may download the Practice Workbook and practice yourself.
Conclusion
In this article, I’ve covered the use of the MIN function. I’ve especially focused on those scenarios where the Excel MIN function returns 0. I’ve also demonstrated how we can handle these situations. In the end, I can say that, if you’ve followed along with me till now, you can now use the MIN function more comfortably. I’ve tried to keep this article as elaborately explained as possible to ease your understanding. Having said that, if you find any difficulty using the MIN function, please let me know in the comment section. I’ll be glad to help you with this.
Related Articles
- How to Find Minimum Value Based on Multiple Criteria in Excel
- How to Find Minimum Value with VLOOKUP in Excel
- How to Use Combined MIN and IF Function in Excel
- Difference Between MAX and MIN Function in Excel
<< Go Back to Excel MIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
